eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ISC_DBI_MSC_OBJECTS_C dependencies on BIS_COLLECTION_UTILITIES

Line 45: BIS_COLLECTION_UTILITIES.put_line('The partition plan_' || p_plan_id || ' of table '|| g_large_bases(j) ||' does not exist.');

41: l_drop_stmt := 'ALTER TABLE '|| g_isc_schema || '.' || g_large_bases(j) || ' DROP PARTITION plan_' || p_plan_id;
42: EXECUTE IMMEDIATE l_drop_stmt;
43: EXCEPTION
44: WHEN no_partition THEN
45: BIS_COLLECTION_UTILITIES.put_line('The partition plan_' || p_plan_id || ' of table '|| g_large_bases(j) ||' does not exist.');
46: END;
47: END LOOP;
48:
49: FOR i in 1..g_small_bases.last LOOP

Line 79: BIS_COLLECTION_UTILITIES.put_line('The partition s_'||p_snapshot_id||' of table '||g_large_snapshots(j)||' does not exist.');

75: EXECUTE IMMEDIATE l_drop_stmt;
76:
77: EXCEPTION
78: WHEN no_partition THEN
79: BIS_COLLECTION_UTILITIES.put_line('The partition s_'||p_snapshot_id||' of table '||g_large_snapshots(j)||' does not exist.');
80: END;
81: END LOOP;
82:
83: g_rebuild_snapshot_index := 'Y';

Line 116: BIS_COLLECTION_UTILITIES.Put_Line(' ');

112: l_sec_curr_def VARCHAR2(1);
113:
114: BEGIN
115:
116: BIS_COLLECTION_UTILITIES.Put_Line(' ');
117:
118: IF (NOT FND_INSTALLATION.GET_APP_INFO('ISC', g_status, g_industry, g_isc_schema)) THEN
119: g_errbuf := 'Error while retrieving product information.';
120: RETURN (-1);

Line 129: BIS_COLLECTION_UTILITIES.put_line('The schema name is '|| g_isc_schema);

125: g_errbuf := '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.';
126: return(-1);
127: END IF;
128:
129: BIS_COLLECTION_UTILITIES.put_line('The schema name is '|| g_isc_schema);
130:
131: g_batch_size := bis_common_parameters.get_batch_size(bis_common_parameters.high);
132: BIS_COLLECTION_UTILITIES.put_line('The batch size is ' || g_batch_size);
133:

Line 132: BIS_COLLECTION_UTILITIES.put_line('The batch size is ' || g_batch_size);

128:
129: BIS_COLLECTION_UTILITIES.put_line('The schema name is '|| g_isc_schema);
130:
131: g_batch_size := bis_common_parameters.get_batch_size(bis_common_parameters.high);
132: BIS_COLLECTION_UTILITIES.put_line('The batch size is ' || g_batch_size);
133:
134: g_global_currency := bis_common_parameters.get_currency_code;
135: BIS_COLLECTION_UTILITIES.put_line('The global currency code is ' || g_global_currency);
136:

Line 135: BIS_COLLECTION_UTILITIES.put_line('The global currency code is ' || g_global_currency);

131: g_batch_size := bis_common_parameters.get_batch_size(bis_common_parameters.high);
132: BIS_COLLECTION_UTILITIES.put_line('The batch size is ' || g_batch_size);
133:
134: g_global_currency := bis_common_parameters.get_currency_code;
135: BIS_COLLECTION_UTILITIES.put_line('The global currency code is ' || g_global_currency);
136:
137: g_global_rate_type := bis_common_parameters.get_rate_type;
138: BIS_COLLECTION_UTILITIES.put_line('The primary rate type is ' || g_global_rate_type);
139:

Line 138: BIS_COLLECTION_UTILITIES.put_line('The primary rate type is ' || g_global_rate_type);

134: g_global_currency := bis_common_parameters.get_currency_code;
135: BIS_COLLECTION_UTILITIES.put_line('The global currency code is ' || g_global_currency);
136:
137: g_global_rate_type := bis_common_parameters.get_rate_type;
138: BIS_COLLECTION_UTILITIES.put_line('The primary rate type is ' || g_global_rate_type);
139:
140: g_sec_global_currency := bis_common_parameters.get_secondary_currency_code;
141: BIS_COLLECTION_UTILITIES.put_line('The secondary global currency code is ' || g_sec_global_currency);
142:

Line 141: BIS_COLLECTION_UTILITIES.put_line('The secondary global currency code is ' || g_sec_global_currency);

137: g_global_rate_type := bis_common_parameters.get_rate_type;
138: BIS_COLLECTION_UTILITIES.put_line('The primary rate type is ' || g_global_rate_type);
139:
140: g_sec_global_currency := bis_common_parameters.get_secondary_currency_code;
141: BIS_COLLECTION_UTILITIES.put_line('The secondary global currency code is ' || g_sec_global_currency);
142:
143: g_sec_global_rate_type := bis_common_parameters.get_secondary_rate_type;
144: BIS_COLLECTION_UTILITIES.put_line('The secondary rate type is ' || g_sec_global_rate_type);
145:

Line 144: BIS_COLLECTION_UTILITIES.put_line('The secondary rate type is ' || g_sec_global_rate_type);

140: g_sec_global_currency := bis_common_parameters.get_secondary_currency_code;
141: BIS_COLLECTION_UTILITIES.put_line('The secondary global currency code is ' || g_sec_global_currency);
142:
143: g_sec_global_rate_type := bis_common_parameters.get_secondary_rate_type;
144: BIS_COLLECTION_UTILITIES.put_line('The secondary rate type is ' || g_sec_global_rate_type);
145:
146: -- To get the database link to APS instance
147: -- If the db link is NULL,
148: -- that means APS and ERP are sitting in the same instance

Line 171: BIS_COLLECTION_UTILITIES.put_line('Retrieve the db link '|| g_db_link);

167: ELSIF (g_db_link = '@') THEN
168: g_db_link := NULL;
169: END IF;
170:
171: BIS_COLLECTION_UTILITIES.put_line('Retrieve the db link '|| g_db_link);
172:
173: l_stmt := 'SELECT count(*) FROM msc_apps_instances' || g_db_link ||
174: ' WHERE enable_flag = 1 AND apps_ver NOT IN (1,2)';
175:

Line 177: BIS_COLLECTION_UTILITIES.put_line('The number of ERP instances is '|| l_num_all_sources);

173: l_stmt := 'SELECT count(*) FROM msc_apps_instances' || g_db_link ||
174: ' WHERE enable_flag = 1 AND apps_ver NOT IN (1,2)';
175:
176: EXECUTE IMMEDIATE l_stmt INTO l_num_all_sources;
177: BIS_COLLECTION_UTILITIES.put_line('The number of ERP instances is '|| l_num_all_sources);
178:
179: IF (l_num_all_sources > 1) THEN
180: g_errbuf := 'There are more than one ERP sources. DBI only supports one ERP source.';
181: RETURN (-1);

Line 190: BIS_COLLECTION_UTILITIES.put_line('Begin to collect instance information');

186:
187: l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_apps_instances';
188: EXECUTE IMMEDIATE l_trunc_stmt;
189:
190: BIS_COLLECTION_UTILITIES.put_line('Begin to collect instance information');
191: FII_UTIL.Start_Timer;
192:
193: l_stmt := 'INSERT INTO isc_dbi_apps_instances (' ||
194: ' instance_id, currency, instance_code, ' ||

Line 213: BIS_COLLECTION_UTILITIES.Put_Line(' ');

209: END IF;
210:
211: FII_UTIL.Stop_Timer;
212: FII_UTIL.Print_Timer('Collected instance information in');
213: BIS_COLLECTION_UTILITIES.Put_Line(' ');
214:
215: /* From DBI7.0, functional and DBI global currencies will be used. */
216:
217: -- BIS_COLLECTION_UTILITIES.put_line('Begin to validate APS currency.');

Line 217: -- BIS_COLLECTION_UTILITIES.put_line('Begin to validate APS currency.');

213: BIS_COLLECTION_UTILITIES.Put_Line(' ');
214:
215: /* From DBI7.0, functional and DBI global currencies will be used. */
216:
217: -- BIS_COLLECTION_UTILITIES.put_line('Begin to validate APS currency.');
218: -- FII_UTIL.Start_Timer;
219: --
220: -- SELECT count(*)
221: -- INTO l_currency

Line 228: -- BIS_COLLECTION_UTILITIES.Put_Line('The planning currency code is invalid.');

224: --
225: -- IF (l_currency = 0) THEN
226: -- g_retcode := 1;
227: -- g_errbuf := 'The planning currency code is invalid.';
228: -- BIS_COLLECTION_UTILITIES.Put_Line('The planning currency code is invalid.');
229: -- BIS_COLLECTION_UTILITIES.Put_Line(' ');
230: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
231: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_INVALID_CURRENCY'));
232: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');

Line 229: -- BIS_COLLECTION_UTILITIES.Put_Line(' ');

225: -- IF (l_currency = 0) THEN
226: -- g_retcode := 1;
227: -- g_errbuf := 'The planning currency code is invalid.';
228: -- BIS_COLLECTION_UTILITIES.Put_Line('The planning currency code is invalid.');
229: -- BIS_COLLECTION_UTILITIES.Put_Line(' ');
230: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
231: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_INVALID_CURRENCY'));
232: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
233: -- END IF;

Line 230: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');

226: -- g_retcode := 1;
227: -- g_errbuf := 'The planning currency code is invalid.';
228: -- BIS_COLLECTION_UTILITIES.Put_Line('The planning currency code is invalid.');
229: -- BIS_COLLECTION_UTILITIES.Put_Line(' ');
230: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
231: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_INVALID_CURRENCY'));
232: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
233: -- END IF;
234: --

Line 231: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_INVALID_CURRENCY'));

227: -- g_errbuf := 'The planning currency code is invalid.';
228: -- BIS_COLLECTION_UTILITIES.Put_Line('The planning currency code is invalid.');
229: -- BIS_COLLECTION_UTILITIES.Put_Line(' ');
230: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
231: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_INVALID_CURRENCY'));
232: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
233: -- END IF;
234: --
235: -- FII_UTIL.Stop_Timer;

Line 232: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');

228: -- BIS_COLLECTION_UTILITIES.Put_Line('The planning currency code is invalid.');
229: -- BIS_COLLECTION_UTILITIES.Put_Line(' ');
230: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
231: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_INVALID_CURRENCY'));
232: -- BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
233: -- END IF;
234: --
235: -- FII_UTIL.Stop_Timer;
236: -- FII_UTIL.Print_Timer('Validated the planning currency in');

Line 237: -- BIS_COLLECTION_UTILITIES.Put_Line(' ');

233: -- END IF;
234: --
235: -- FII_UTIL.Stop_Timer;
236: -- FII_UTIL.Print_Timer('Validated the planning currency in');
237: -- BIS_COLLECTION_UTILITIES.Put_Line(' ');
238:
239:
240: RETURN(1);
241:

Line 273: BIS_COLLECTION_UTILITIES.put_line('Truncating the temp table');

269: -- ------------------------------------------------------------
270: -- Insert the plans need to be collected into ISC_DBI_TMP_PLANS
271: -- ------------------------------------------------------------
272:
273: BIS_COLLECTION_UTILITIES.put_line('Truncating the temp table');
274: FII_UTIL.Start_Timer;
275:
276: l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_tmp_plans';
277: EXECUTE IMMEDIATE l_trunc_stmt;

Line 281: BIS_COLLECTION_UTILITIES.Put_Line(' ');

277: EXECUTE IMMEDIATE l_trunc_stmt;
278:
279: FII_UTIL.Stop_Timer;
280: FII_UTIL.Print_Timer('Truncated the temp table in');
281: BIS_COLLECTION_UTILITIES.Put_Line(' ');
282:
283: g_snapshot_date := sysdate;
284: BIS_COLLECTION_UTILITIES.put_line('The snapshot date is '|| to_char(g_snapshot_date, 'MM/DD/YYYY HH24:MI:SS'));
285:

Line 284: BIS_COLLECTION_UTILITIES.put_line('The snapshot date is '|| to_char(g_snapshot_date, 'MM/DD/YYYY HH24:MI:SS'));

280: FII_UTIL.Print_Timer('Truncated the temp table in');
281: BIS_COLLECTION_UTILITIES.Put_Line(' ');
282:
283: g_snapshot_date := sysdate;
284: BIS_COLLECTION_UTILITIES.put_line('The snapshot date is '|| to_char(g_snapshot_date, 'MM/DD/YYYY HH24:MI:SS'));
285:
286: BIS_COLLECTION_UTILITIES.put_line('Begin to load into the temp table.');
287: FII_UTIL.Start_Timer;
288:

Line 286: BIS_COLLECTION_UTILITIES.put_line('Begin to load into the temp table.');

282:
283: g_snapshot_date := sysdate;
284: BIS_COLLECTION_UTILITIES.put_line('The snapshot date is '|| to_char(g_snapshot_date, 'MM/DD/YYYY HH24:MI:SS'));
285:
286: BIS_COLLECTION_UTILITIES.put_line('Begin to load into the temp table.');
287: FII_UTIL.Start_Timer;
288:
289: l_stmt := 'INSERT INTO isc_dbi_tmp_plans (' ||
290: 'PLAN_ID, PLAN_NAME, OLD_DATA_START_DATE, DATA_START_DATE, ' ||

Line 318: BIS_COLLECTION_UTILITIES.Put_Line(' ');

314: COMMIT;
315:
316: FII_UTIL.Stop_Timer;
317: FII_UTIL.Print_Timer('Retrieved '|| l_count || ' plans from setup tables in');
318: BIS_COLLECTION_UTILITIES.Put_Line(' ');
319:
320: BIS_COLLECTION_UTILITIES.Put_Line(' ');
321: BIS_COLLECTION_UTILITIES.Put_Line('Analyzing table ISC_DBI_TMP_PLANS');
322: FII_UTIL.Start_Timer;

Line 320: BIS_COLLECTION_UTILITIES.Put_Line(' ');

316: FII_UTIL.Stop_Timer;
317: FII_UTIL.Print_Timer('Retrieved '|| l_count || ' plans from setup tables in');
318: BIS_COLLECTION_UTILITIES.Put_Line(' ');
319:
320: BIS_COLLECTION_UTILITIES.Put_Line(' ');
321: BIS_COLLECTION_UTILITIES.Put_Line('Analyzing table ISC_DBI_TMP_PLANS');
322: FII_UTIL.Start_Timer;
323:
324: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,

Line 321: BIS_COLLECTION_UTILITIES.Put_Line('Analyzing table ISC_DBI_TMP_PLANS');

317: FII_UTIL.Print_Timer('Retrieved '|| l_count || ' plans from setup tables in');
318: BIS_COLLECTION_UTILITIES.Put_Line(' ');
319:
320: BIS_COLLECTION_UTILITIES.Put_Line(' ');
321: BIS_COLLECTION_UTILITIES.Put_Line('Analyzing table ISC_DBI_TMP_PLANS');
322: FII_UTIL.Start_Timer;
323:
324: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
325: TABNAME => 'ISC_DBI_TMP_PLANS');

Line 337: BIS_COLLECTION_UTILITIES.Put_Line('Dropping plan '||l_plan_id);

333: OPEN Obsolete_Plans;
334: FETCH Obsolete_Plans INTO l_plan_id;
335: IF Obsolete_Plans%ROWCOUNT <> 0 THEN
336: WHILE Obsolete_Plans%Found LOOP
337: BIS_COLLECTION_UTILITIES.Put_Line('Dropping plan '||l_plan_id);
338: IF (DROP_PLANS(l_plan_id) = -1) THEN RETURN(-1); END IF;
339: l_count := l_count +1;
340: FETCH Obsolete_Plans INTO l_plan_id;
341: END LOOP;

Line 352: BIS_COLLECTION_UTILITIES.Put_Line('Identified '|| l_count || ' plans need to be collected.');

348: FROM isc_dbi_tmp_plans tmp
349: WHERE nvl(tmp.old_data_start_date, tmp.data_start_date-1) < tmp.data_start_date;
350: */
351:
352: BIS_COLLECTION_UTILITIES.Put_Line('Identified '|| l_count || ' plans need to be collected.');
353: RETURN(l_count);
354:
355: EXCEPTION
356: WHEN OTHERS THEN

Line 395: BIS_COLLECTION_UTILITIES.Put_Line('Adding partitions for plan '|| l_add_plan_id);

391: OPEN Plan_List;
392: FETCH Plan_List INTO l_add_plan_id;
393: IF Plan_List%ROWCOUNT <> 0 THEN
394: WHILE Plan_List%Found LOOP
395: BIS_COLLECTION_UTILITIES.Put_Line('Adding partitions for plan '|| l_add_plan_id);
396: FOR i in 1..g_large_bases.last LOOP
397: BEGIN
398: l_add_stmt := 'ALTER TABLE '||g_isc_schema||'.'||g_large_bases(i)||' ADD PARTITION plan_'|| l_add_plan_id ||' VALUES ('''||l_add_plan_id||''')';
399: EXECUTE IMMEDIATE l_add_stmt;

Line 402: BIS_COLLECTION_UTILITIES.put_line('The partition plan_'||l_add_plan_id||' of table '||g_large_bases(i)||' already exists.');

398: l_add_stmt := 'ALTER TABLE '||g_isc_schema||'.'||g_large_bases(i)||' ADD PARTITION plan_'|| l_add_plan_id ||' VALUES ('''||l_add_plan_id||''')';
399: EXECUTE IMMEDIATE l_add_stmt;
400: EXCEPTION
401: WHEN partition_exists THEN
402: BIS_COLLECTION_UTILITIES.put_line('The partition plan_'||l_add_plan_id||' of table '||g_large_bases(i)||' already exists.');
403: NULL;
404: WHEN part_value_exists THEN
405: BIS_COLLECTION_UTILITIES.put_line('The value '||l_add_plan_id||' already exists in another partition of table '||g_large_bases(i));
406: NULL;

Line 405: BIS_COLLECTION_UTILITIES.put_line('The value '||l_add_plan_id||' already exists in another partition of table '||g_large_bases(i));

401: WHEN partition_exists THEN
402: BIS_COLLECTION_UTILITIES.put_line('The partition plan_'||l_add_plan_id||' of table '||g_large_bases(i)||' already exists.');
403: NULL;
404: WHEN part_value_exists THEN
405: BIS_COLLECTION_UTILITIES.put_line('The value '||l_add_plan_id||' already exists in another partition of table '||g_large_bases(i));
406: NULL;
407: END;
408: END LOOP;
409: FETCH Plan_List INTO l_add_plan_id;

Line 417: BIS_COLLECTION_UTILITIES.put_line('Table isc_dbi_periods has been truncated.');

413: CLOSE Plan_List;
414:
415: l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_periods';
416: EXECUTE IMMEDIATE l_trunc_stmt;
417: BIS_COLLECTION_UTILITIES.put_line('Table isc_dbi_periods has been truncated.');
418:
419: BIS_COLLECTION_UTILITIES.put_line('Begin to load the base tables from APS instance.');
420: FII_UTIL.Start_Timer;
421:

Line 419: BIS_COLLECTION_UTILITIES.put_line('Begin to load the base tables from APS instance.');

415: l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_periods';
416: EXECUTE IMMEDIATE l_trunc_stmt;
417: BIS_COLLECTION_UTILITIES.put_line('Table isc_dbi_periods has been truncated.');
418:
419: BIS_COLLECTION_UTILITIES.put_line('Begin to load the base tables from APS instance.');
420: FII_UTIL.Start_Timer;
421:
422: l_insert_stmt := 'INSERT /*+ APPEND PARALLEL */ FIRST '||
423: 'WHEN union_flag = 1 THEN '||

Line 913: BIS_COLLECTION_UTILITIES.Put_Line('The length of the insert statement is '|| l_in_length);

909: 'AND nvl(tmp.old_data_start_date,tmp.data_start_date-1) < tmp.data_start_date '||
910: 'AND tmp.instance_id = pg.sr_instance_id';
911:
912: l_in_length := length(l_insert_stmt);
913: BIS_COLLECTION_UTILITIES.Put_Line('The length of the insert statement is '|| l_in_length);
914: l_sel_length1 := length(l_sel_stmt1);
915: BIS_COLLECTION_UTILITIES.Put_Line('The length of the select statement 1 is '|| l_sel_length1);
916: l_sel_length2 := length(l_sel_stmt2);
917: BIS_COLLECTION_UTILITIES.Put_Line('The length of the select statement 2 is '|| l_sel_length2);

Line 915: BIS_COLLECTION_UTILITIES.Put_Line('The length of the select statement 1 is '|| l_sel_length1);

911:
912: l_in_length := length(l_insert_stmt);
913: BIS_COLLECTION_UTILITIES.Put_Line('The length of the insert statement is '|| l_in_length);
914: l_sel_length1 := length(l_sel_stmt1);
915: BIS_COLLECTION_UTILITIES.Put_Line('The length of the select statement 1 is '|| l_sel_length1);
916: l_sel_length2 := length(l_sel_stmt2);
917: BIS_COLLECTION_UTILITIES.Put_Line('The length of the select statement 2 is '|| l_sel_length2);
918:
919: EXECUTE IMMEDIATE (l_insert_stmt || l_sel_stmt1 || l_sel_stmt2);

Line 917: BIS_COLLECTION_UTILITIES.Put_Line('The length of the select statement 2 is '|| l_sel_length2);

913: BIS_COLLECTION_UTILITIES.Put_Line('The length of the insert statement is '|| l_in_length);
914: l_sel_length1 := length(l_sel_stmt1);
915: BIS_COLLECTION_UTILITIES.Put_Line('The length of the select statement 1 is '|| l_sel_length1);
916: l_sel_length2 := length(l_sel_stmt2);
917: BIS_COLLECTION_UTILITIES.Put_Line('The length of the select statement 2 is '|| l_sel_length2);
918:
919: EXECUTE IMMEDIATE (l_insert_stmt || l_sel_stmt1 || l_sel_stmt2);
920: l_count := l_count + sql%rowcount;
921: COMMIT;

Line 925: BIS_COLLECTION_UTILITIES.Put_Line(' ');

921: COMMIT;
922:
923: FII_UTIL.Stop_Timer;
924: FII_UTIL.Print_Timer('Loaded the base tables in');
925: BIS_COLLECTION_UTILITIES.Put_Line(' ');
926:
927: UPDATE isc_dbi_tmp_plans tmp SET constrained_flag = (select constrained_flag from isc_dbi_plans p where p.plan_id = tmp.plan_id);
928: COMMIT;
929:

Line 948: BIS_COLLECTION_UTILITIES.put_line('Table isc_dbi_plan_curr_rates has been truncated.');

944: FII_UTIL.Print_Timer('Analyzed the base tables in');
945:
946: l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_plan_curr_rates';
947: EXECUTE IMMEDIATE l_trunc_stmt;
948: BIS_COLLECTION_UTILITIES.put_line('Table isc_dbi_plan_curr_rates has been truncated.');
949:
950: BIS_COLLECTION_UTILITIES.put_line('Begin to retrieve the currency conversion rates.');
951: FII_UTIL.Start_Timer;
952:

Line 950: BIS_COLLECTION_UTILITIES.put_line('Begin to retrieve the currency conversion rates.');

946: l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_plan_curr_rates';
947: EXECUTE IMMEDIATE l_trunc_stmt;
948: BIS_COLLECTION_UTILITIES.put_line('Table isc_dbi_plan_curr_rates has been truncated.');
949:
950: BIS_COLLECTION_UTILITIES.put_line('Begin to retrieve the currency conversion rates.');
951: FII_UTIL.Start_Timer;
952:
953: INSERT INTO isc_dbi_plan_curr_rates(
954: ORGANIZATION_ID,

Line 1020: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');

1016: FII_UTIL.Print_Timer('Retrieved the min and max date in ');
1017:
1018: FII_UTIL.Start_Timer;
1019:
1020: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1021: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1022:
1023: IF(l_min IS NOT NULL and l_max IS NOT NULL) THEN
1024: FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);

Line 1021: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');

1017:
1018: FII_UTIL.Start_Timer;
1019:
1020: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1021: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1022:
1023: IF(l_min IS NOT NULL and l_max IS NOT NULL) THEN
1024: FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1025:

Line 1027: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for time dimension.');

1023: IF(l_min IS NOT NULL and l_max IS NOT NULL) THEN
1024: FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1025:
1026: IF (l_is_missing) THEN
1027: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for time dimension.');
1028: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');
1029: RETURN (-999);
1030: ELSE
1031: BIS_COLLECTION_UTILITIES.Put_Line(' ');

Line 1028: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');

1024: FII_TIME_API.check_missing_date(l_min, l_max, l_is_missing);
1025:
1026: IF (l_is_missing) THEN
1027: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for time dimension.');
1028: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');
1029: RETURN (-999);
1030: ELSE
1031: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1032: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');

Line 1031: BIS_COLLECTION_UTILITIES.Put_Line(' ');

1027: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for time dimension.');
1028: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');
1029: RETURN (-999);
1030: ELSE
1031: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1032: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1033: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1034: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1035: END IF;

Line 1032: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');

1028: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded.');
1029: RETURN (-999);
1030: ELSE
1031: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1032: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1033: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1034: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1035: END IF;
1036: END IF;

Line 1033: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');

1029: RETURN (-999);
1030: ELSE
1031: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1032: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1033: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1034: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1035: END IF;
1036: END IF;
1037:

Line 1034: BIS_COLLECTION_UTILITIES.Put_Line(' ');

1030: ELSE
1031: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1032: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1033: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1034: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1035: END IF;
1036: END IF;
1037:
1038: FII_UTIL.Stop_Timer;

Line 1094: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');

1090: OPEN Dangling_Items;
1091: FETCH Dangling_Items INTO l_item, l_org;
1092:
1093: IF Dangling_Items%ROWCOUNT <> 0 THEN
1094: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1095: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1096:
1097: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1098: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');

Line 1095: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');

1091: FETCH Dangling_Items INTO l_item, l_org;
1092:
1093: IF Dangling_Items%ROWCOUNT <> 0 THEN
1094: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1095: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1096:
1097: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1098: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1099: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));

Line 1097: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');

1093: IF Dangling_Items%ROWCOUNT <> 0 THEN
1094: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1095: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1096:
1097: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1098: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1099: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1100: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1101: 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,' '));

Line 1098: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');

1094: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are dangling keys for item dimension.');
1095: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1096:
1097: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1098: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1099: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1100: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1101: 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,' '));
1102: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');

Line 1099: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));

1095: BIS_COLLECTION_UTILITIES.Put_Line('No records were loaded');
1096:
1097: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1098: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1099: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1100: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1101: 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,' '));
1102: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1103:

Line 1100: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');

1096:
1097: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1098: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1099: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1100: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1101: 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,' '));
1102: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1103:
1104: WHILE Dangling_Items%FOUND LOOP

Line 1101: 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,' '));

1097: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1098: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1099: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1100: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1101: 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,' '));
1102: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1103:
1104: WHILE Dangling_Items%FOUND LOOP
1105: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,18,' ')||' - '||RPAD(l_org,18,' '));

Line 1102: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');

1098: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1099: BIS_COLLECTION_UTILITIES.Put_Line_Out(fnd_message.get_string('ISC', 'ISC_DBI_ITEM_NO_LOAD'));
1100: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1101: 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,' '));
1102: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1103:
1104: WHILE Dangling_Items%FOUND LOOP
1105: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,18,' ')||' - '||RPAD(l_org,18,' '));
1106: FETCH Dangling_Items INTO l_item, l_org;

Line 1105: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,18,' ')||' - '||RPAD(l_org,18,' '));

1101: 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,' '));
1102: BIS_COLLECTION_UTILITIES.Put_Line_Out('----------------------- - --------------------');
1103:
1104: WHILE Dangling_Items%FOUND LOOP
1105: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,18,' ')||' - '||RPAD(l_org,18,' '));
1106: FETCH Dangling_Items INTO l_item, l_org;
1107: END LOOP;
1108: BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------+');
1109: ELSE

Line 1108: BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------+');

1104: WHILE Dangling_Items%FOUND LOOP
1105: BIS_COLLECTION_UTILITIES.Put_Line_Out(RPAD(l_item,18,' ')||' - '||RPAD(l_org,18,' '));
1106: FETCH Dangling_Items INTO l_item, l_org;
1107: END LOOP;
1108: BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------+');
1109: ELSE
1110: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1111: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1112: BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');

Line 1110: BIS_COLLECTION_UTILITIES.Put_Line(' ');

1106: FETCH Dangling_Items INTO l_item, l_org;
1107: END LOOP;
1108: BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------+');
1109: ELSE
1110: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1111: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1112: BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1113: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1114: END IF;

Line 1111: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');

1107: END LOOP;
1108: BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------+');
1109: ELSE
1110: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1111: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1112: BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1113: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1114: END IF;
1115: l_total := Dangling_Items%ROWCOUNT;

Line 1112: BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');

1108: BIS_COLLECTION_UTILITIES.Put_Line_Out('+-------------------------------------+');
1109: ELSE
1110: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1111: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1112: BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1113: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1114: END IF;
1115: l_total := Dangling_Items%ROWCOUNT;
1116: CLOSE Dangling_Items;

Line 1113: BIS_COLLECTION_UTILITIES.Put_Line(' ');

1109: ELSE
1110: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1111: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO DANGLING ITEMS ');
1112: BIS_COLLECTION_UTILITIES.Put_Line('+--------------------------------------------+');
1113: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1114: END IF;
1115: l_total := Dangling_Items%ROWCOUNT;
1116: CLOSE Dangling_Items;
1117:

Line 1163: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing currency conversion rates.');

1159: FETCH Missing_Currency_Conversion INTO l_record;
1160:
1161: IF Missing_Currency_Conversion%ROWCOUNT <> 0
1162: THEN
1163: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing currency conversion rates.');
1164: BIS_COLLECTION_UTILITIES.Put_Line(fnd_message.get_string('BIS', 'BIS_DBI_CURR_NO_LOAD'));
1165:
1166: BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
1167: WHILE Missing_Currency_Conversion%FOUND LOOP

Line 1164: BIS_COLLECTION_UTILITIES.Put_Line(fnd_message.get_string('BIS', 'BIS_DBI_CURR_NO_LOAD'));

1160:
1161: IF Missing_Currency_Conversion%ROWCOUNT <> 0
1162: THEN
1163: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing currency conversion rates.');
1164: BIS_COLLECTION_UTILITIES.Put_Line(fnd_message.get_string('BIS', 'BIS_DBI_CURR_NO_LOAD'));
1165:
1166: BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
1167: WHILE Missing_Currency_Conversion%FOUND LOOP
1168: l_total := l_total + 1;

Line 1166: BIS_COLLECTION_UTILITIES.writeMissingRateHeader;

1162: THEN
1163: BIS_COLLECTION_UTILITIES.Put_Line('Collection failed because there are missing currency conversion rates.');
1164: BIS_COLLECTION_UTILITIES.Put_Line(fnd_message.get_string('BIS', 'BIS_DBI_CURR_NO_LOAD'));
1165:
1166: BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
1167: WHILE Missing_Currency_Conversion%FOUND LOOP
1168: l_total := l_total + 1;
1169: BIS_COLLECTION_UTILITIES.writeMissingRate(
1170: l_record.rate_type,

Line 1169: BIS_COLLECTION_UTILITIES.writeMissingRate(

1165:
1166: BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
1167: WHILE Missing_Currency_Conversion%FOUND LOOP
1168: l_total := l_total + 1;
1169: BIS_COLLECTION_UTILITIES.writeMissingRate(
1170: l_record.rate_type,
1171: l_record.from_currency,
1172: l_record.to_currency,
1173: l_record.curr_conv_date);

Line 1176: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');

1172: l_record.to_currency,
1173: l_record.curr_conv_date);
1174: FETCH Missing_Currency_Conversion INTO l_record;
1175: END LOOP;
1176: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1177: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1178:
1179: ELSE -- Missing_Currency_Conversion%ROWCOUNT = 0
1180: BIS_COLLECTION_UTILITIES.Put_Line(' ');

Line 1177: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');

1173: l_record.curr_conv_date);
1174: FETCH Missing_Currency_Conversion INTO l_record;
1175: END LOOP;
1176: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1177: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1178:
1179: ELSE -- Missing_Currency_Conversion%ROWCOUNT = 0
1180: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1181: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');

Line 1180: BIS_COLLECTION_UTILITIES.Put_Line(' ');

1176: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1177: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1178:
1179: ELSE -- Missing_Currency_Conversion%ROWCOUNT = 0
1180: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1181: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');
1182: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1183: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1184: END IF; -- Missing_Currency_Conversion%ROWCOUNT <> 0

Line 1181: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');

1177: BIS_COLLECTION_UTILITIES.Put_Line_Out(' ');
1178:
1179: ELSE -- Missing_Currency_Conversion%ROWCOUNT = 0
1180: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1181: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');
1182: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1183: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1184: END IF; -- Missing_Currency_Conversion%ROWCOUNT <> 0
1185:

Line 1182: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');

1178:
1179: ELSE -- Missing_Currency_Conversion%ROWCOUNT = 0
1180: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1181: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');
1182: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1183: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1184: END IF; -- Missing_Currency_Conversion%ROWCOUNT <> 0
1185:
1186: CLOSE Missing_Currency_Conversion;

Line 1183: BIS_COLLECTION_UTILITIES.Put_Line(' ');

1179: ELSE -- Missing_Currency_Conversion%ROWCOUNT = 0
1180: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1181: BIS_COLLECTION_UTILITIES.Put_Line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');
1182: BIS_COLLECTION_UTILITIES.Put_Line('+---------------------------------------------------------------------------+');
1183: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1184: END IF; -- Missing_Currency_Conversion%ROWCOUNT <> 0
1185:
1186: CLOSE Missing_Currency_Conversion;
1187:

Line 1215: BIS_COLLECTION_UTILITIES.put_line(' ');

1211: -- Identify Missing Currency Rate from ISC_DBI_PLAN_CURR_RATES
1212: -- When there is missing rate, exit the collection with error
1213: -- ----------------------------------------------------------
1214:
1215: BIS_COLLECTION_UTILITIES.put_line(' ');
1216: BIS_COLLECTION_UTILITIES.put_line('Identifying the missing currency conversion rates');
1217: FII_UTIL.Start_Timer;
1218:
1219: l_miss_conv := REPORT_MISSING_RATE;

Line 1216: BIS_COLLECTION_UTILITIES.put_line('Identifying the missing currency conversion rates');

1212: -- When there is missing rate, exit the collection with error
1213: -- ----------------------------------------------------------
1214:
1215: BIS_COLLECTION_UTILITIES.put_line(' ');
1216: BIS_COLLECTION_UTILITIES.put_line('Identifying the missing currency conversion rates');
1217: FII_UTIL.Start_Timer;
1218:
1219: l_miss_conv := REPORT_MISSING_RATE;
1220:

Line 1235: BIS_COLLECTION_UTILITIES.Put_Line(' ');

1231: -- ---------------------
1232: -- CHECK_TIME_CONTINUITY
1233: -- ---------------------
1234:
1235: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1236: BIS_COLLECTION_UTILITIES.put_line('Checking Time Continuity');
1237:
1238: l_time_danling := check_time_continuity;
1239:

Line 1236: BIS_COLLECTION_UTILITIES.put_line('Checking Time Continuity');

1232: -- CHECK_TIME_CONTINUITY
1233: -- ---------------------
1234:
1235: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1236: BIS_COLLECTION_UTILITIES.put_line('Checking Time Continuity');
1237:
1238: l_time_danling := check_time_continuity;
1239:
1240: IF (l_time_danling = -1) THEN

Line 1251: BIS_COLLECTION_UTILITIES.put_line(' ');

1247: -- -------------------------------------
1248: -- Check Dangling Key for Item Dimension
1249: -- -------------------------------------
1250:
1251: BIS_COLLECTION_UTILITIES.put_line(' ');
1252: BIS_COLLECTION_UTILITIES.put_line('Identifying the dangling items');
1253:
1254: FII_UTIL.Start_Timer;
1255:

Line 1252: BIS_COLLECTION_UTILITIES.put_line('Identifying the dangling items');

1248: -- Check Dangling Key for Item Dimension
1249: -- -------------------------------------
1250:
1251: BIS_COLLECTION_UTILITIES.put_line(' ');
1252: BIS_COLLECTION_UTILITIES.put_line('Identifying the dangling items');
1253:
1254: FII_UTIL.Start_Timer;
1255:
1256: l_item_count := IDENTIFY_DANGLING_ITEM;

Line 1307: BIS_COLLECTION_UTILITIES.put_line(' ');

1303: -- ----------------------------------------------------
1304: -- Truncate all the base tables before the initial load
1305: -- ----------------------------------------------------
1306:
1307: BIS_COLLECTION_UTILITIES.put_line(' ');
1308: BIS_COLLECTION_UTILITIES.put_line('Begin to truncate all the base tables');
1309:
1310: FOR j in 1..g_large_bases.last LOOP
1311: l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.'|| g_large_bases(j);

Line 1308: BIS_COLLECTION_UTILITIES.put_line('Begin to truncate all the base tables');

1304: -- Truncate all the base tables before the initial load
1305: -- ----------------------------------------------------
1306:
1307: BIS_COLLECTION_UTILITIES.put_line(' ');
1308: BIS_COLLECTION_UTILITIES.put_line('Begin to truncate all the base tables');
1309:
1310: FOR j in 1..g_large_bases.last LOOP
1311: l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.'|| g_large_bases(j);
1312: EXECUTE IMMEDIATE l_trunc_stmt;

Line 1330: BIS_COLLECTION_UTILITIES.Put_Line('Cleaned up the last collected date.');

1326:
1327: UPDATE isc_dbi_plan_schedules
1328: SET last_collected_date = NULL;
1329:
1330: BIS_COLLECTION_UTILITIES.Put_Line('Cleaned up the last collected date.');
1331:
1332: -- --------------------------------------------
1333: -- Identify Plans to be collected
1334: -- --------------------------------------------

Line 1367: BIS_COLLECTION_UTILITIES.put_line(' ');

1363: EXCEPTION
1364:
1365: WHEN L_FAILURE THEN
1366: ROLLBACK;
1367: BIS_COLLECTION_UTILITIES.put_line(' ');
1368: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
1369: RETURN(-1);
1370:
1371: WHEN OTHERS THEN

Line 1368: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);

1364:
1365: WHEN L_FAILURE THEN
1366: ROLLBACK;
1367: BIS_COLLECTION_UTILITIES.put_line(' ');
1368: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
1369: RETURN(-1);
1370:
1371: WHEN OTHERS THEN
1372: ROLLBACK;

Line 1374: BIS_COLLECTION_UTILITIES.put_line(' ');

1370:
1371: WHEN OTHERS THEN
1372: ROLLBACK;
1373: g_errbuf := sqlerrm ||' - '||sqlcode;
1374: BIS_COLLECTION_UTILITIES.put_line(' ');
1375: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
1376: RETURN(-1);
1377:
1378: END load_bases_init;

Line 1375: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);

1371: WHEN OTHERS THEN
1372: ROLLBACK;
1373: g_errbuf := sqlerrm ||' - '||sqlcode;
1374: BIS_COLLECTION_UTILITIES.put_line(' ');
1375: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
1376: RETURN(-1);
1377:
1378: END load_bases_init;
1379:

Line 1431: BIS_COLLECTION_UTILITIES.put_line(' ');

1427: EXCEPTION
1428:
1429: WHEN L_FAILURE THEN
1430: ROLLBACK;
1431: BIS_COLLECTION_UTILITIES.put_line(' ');
1432: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
1433: RETURN(-1);
1434:
1435: WHEN OTHERS THEN

Line 1432: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);

1428:
1429: WHEN L_FAILURE THEN
1430: ROLLBACK;
1431: BIS_COLLECTION_UTILITIES.put_line(' ');
1432: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
1433: RETURN(-1);
1434:
1435: WHEN OTHERS THEN
1436: ROLLBACK;

Line 1438: BIS_COLLECTION_UTILITIES.put_line(' ');

1434:
1435: WHEN OTHERS THEN
1436: ROLLBACK;
1437: g_errbuf := sqlerrm ||' - '||sqlcode;
1438: BIS_COLLECTION_UTILITIES.put_line(' ');
1439: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
1440: RETURN(-1);
1441:
1442: END load_bases;

Line 1439: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);

1435: WHEN OTHERS THEN
1436: ROLLBACK;
1437: g_errbuf := sqlerrm ||' - '||sqlcode;
1438: BIS_COLLECTION_UTILITIES.put_line(' ');
1439: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
1440: RETURN(-1);
1441:
1442: END load_bases;
1443:

Line 1495: BIS_COLLECTION_UTILITIES.Put_Line('Dropping snapshot '|| l_delete_id);

1491: OPEN Delete_List;
1492: FETCH Delete_List INTO l_delete_id;
1493: IF Delete_List%ROWCOUNT <> 0 THEN
1494: WHILE Delete_List%Found LOOP
1495: BIS_COLLECTION_UTILITIES.Put_Line('Dropping snapshot '|| l_delete_id);
1496: IF (drop_snapshots(l_delete_id) = -1) THEN RETURN(-1); END IF;
1497: FETCH Delete_List INTO l_delete_id;
1498: END LOOP;
1499: END IF;

Line 1529: BIS_COLLECTION_UTILITIES.Put_Line(' ');

1525: UPDATE isc_dbi_tmp_plans SET snapshot_id = isc_dbi_msc_objects_s.nextval WHERE bitand(plan_usage, 2) = 2;
1526: l_count := SQL%ROWCOUNT;
1527: COMMIT;
1528:
1529: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1530: BIS_COLLECTION_UTILITIES.Put_Line('Identified '|| l_count || ' snapshots to be collected.');
1531:
1532: IF (l_count > 0) THEN
1533:

Line 1530: BIS_COLLECTION_UTILITIES.Put_Line('Identified '|| l_count || ' snapshots to be collected.');

1526: l_count := SQL%ROWCOUNT;
1527: COMMIT;
1528:
1529: BIS_COLLECTION_UTILITIES.Put_Line(' ');
1530: BIS_COLLECTION_UTILITIES.Put_Line('Identified '|| l_count || ' snapshots to be collected.');
1531:
1532: IF (l_count > 0) THEN
1533:
1534: -- --------------

Line 1544: BIS_COLLECTION_UTILITIES.Put_Line('Adding snapshot '|| l_add_snapshot_id);

1540: OPEN Snapshot_List;
1541: FETCH Snapshot_List INTO l_add_snapshot_id;
1542: IF Snapshot_List%ROWCOUNT <> 0 THEN
1543: WHILE Snapshot_List%Found LOOP
1544: BIS_COLLECTION_UTILITIES.Put_Line('Adding snapshot '|| l_add_snapshot_id);
1545: FOR i in 1..g_large_snapshots.last LOOP
1546: BEGIN
1547: l_add_stmt := 'ALTER TABLE '||g_isc_schema||'.'||g_large_snapshots(i)||' ADD PARTITION s_'|| l_add_snapshot_id ||' VALUES LESS THAN ('''|| to_char(l_add_snapshot_id+1) ||''')';
1548: EXECUTE IMMEDIATE l_add_stmt;

Line 1551: BIS_COLLECTION_UTILITIES.put_line('The partition s_'||l_add_snapshot_id||' of table '||g_large_snapshots(i)||' already exists.');

1547: l_add_stmt := 'ALTER TABLE '||g_isc_schema||'.'||g_large_snapshots(i)||' ADD PARTITION s_'|| l_add_snapshot_id ||' VALUES LESS THAN ('''|| to_char(l_add_snapshot_id+1) ||''')';
1548: EXECUTE IMMEDIATE l_add_stmt;
1549: EXCEPTION
1550: WHEN partition_exists THEN
1551: BIS_COLLECTION_UTILITIES.put_line('The partition s_'||l_add_snapshot_id||' of table '||g_large_snapshots(i)||' already exists.');
1552: NULL;
1553: WHEN part_value_exists THEN
1554: BIS_COLLECTION_UTILITIES.put_line('The value '||l_add_snapshot_id||' already exists in another partition of table '||g_large_snapshots(i));
1555: NULL;

Line 1554: BIS_COLLECTION_UTILITIES.put_line('The value '||l_add_snapshot_id||' already exists in another partition of table '||g_large_snapshots(i));

1550: WHEN partition_exists THEN
1551: BIS_COLLECTION_UTILITIES.put_line('The partition s_'||l_add_snapshot_id||' of table '||g_large_snapshots(i)||' already exists.');
1552: NULL;
1553: WHEN part_value_exists THEN
1554: BIS_COLLECTION_UTILITIES.put_line('The value '||l_add_snapshot_id||' already exists in another partition of table '||g_large_snapshots(i));
1555: NULL;
1556: END;
1557: END LOOP;
1558: FETCH Snapshot_List INTO l_add_snapshot_id;

Line 1569: BIS_COLLECTION_UTILITIES.put_line(' ');

1565: -- ----------------------------------
1566: -- Collect ISC_DBI_PLAN_SNAPSHOTS
1567: -- ----------------------------------
1568:
1569: BIS_COLLECTION_UTILITIES.put_line(' ');
1570: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_plan_snapshots.');
1571: FII_UTIL.Start_Timer;
1572:
1573: INSERT

Line 1570: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_plan_snapshots.');

1566: -- Collect ISC_DBI_PLAN_SNAPSHOTS
1567: -- ----------------------------------
1568:
1569: BIS_COLLECTION_UTILITIES.put_line(' ');
1570: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_plan_snapshots.');
1571: FII_UTIL.Start_Timer;
1572:
1573: INSERT
1574: INTO isc_dbi_plan_snapshots F(

Line 1606: BIS_COLLECTION_UTILITIES.put_line(' ');

1602: -- ----------------------------------
1603: -- Collect ISC_DBI_PLAN_ORG_SNAPSHOTS
1604: -- ----------------------------------
1605:
1606: BIS_COLLECTION_UTILITIES.put_line(' ');
1607: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_plan_org_snapshots.');
1608: FII_UTIL.Start_Timer;
1609:
1610: INSERT

Line 1607: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_plan_org_snapshots.');

1603: -- Collect ISC_DBI_PLAN_ORG_SNAPSHOTS
1604: -- ----------------------------------
1605:
1606: BIS_COLLECTION_UTILITIES.put_line(' ');
1607: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_plan_org_snapshots.');
1608: FII_UTIL.Start_Timer;
1609:
1610: INSERT
1611: INTO isc_dbi_plan_org_snapshots(

Line 1628: BIS_COLLECTION_UTILITIES.put_line(' ');

1624: -- ----------------------------------
1625: -- Collect ISC_DBI_SUPPLIES_SNAPSHOTS
1626: -- ----------------------------------
1627:
1628: BIS_COLLECTION_UTILITIES.put_line(' ');
1629: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_supplies_snapshots.');
1630: FII_UTIL.Start_Timer;
1631:
1632: -- item level only, move category to MV

Line 1629: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_supplies_snapshots.');

1625: -- Collect ISC_DBI_SUPPLIES_SNAPSHOTS
1626: -- ----------------------------------
1627:
1628: BIS_COLLECTION_UTILITIES.put_line(' ');
1629: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_supplies_snapshots.');
1630: FII_UTIL.Start_Timer;
1631:
1632: -- item level only, move category to MV
1633:

Line 1686: BIS_COLLECTION_UTILITIES.put_line(' ');

1682: -- 2 - Resource Related
1683: -- 3 - Transportation Related
1684: -- ----------------------------------------
1685:
1686: BIS_COLLECTION_UTILITIES.put_line(' ');
1687: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_shortfall_snapshots for unconstrained plans.');
1688: FII_UTIL.Start_Timer;
1689:
1690: INSERT

Line 1687: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_shortfall_snapshots for unconstrained plans.');

1683: -- 3 - Transportation Related
1684: -- ----------------------------------------
1685:
1686: BIS_COLLECTION_UTILITIES.put_line(' ');
1687: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_shortfall_snapshots for unconstrained plans.');
1688: FII_UTIL.Start_Timer;
1689:
1690: INSERT
1691: INTO isc_dbi_shortfall_snapshots F(

Line 1794: BIS_COLLECTION_UTILITIES.put_line(' ');

1790:
1791: FII_UTIL.Stop_Timer;
1792: FII_UTIL.Print_Timer('Inserted '|| sql%rowcount ||' rows into isc_dbi_shortfall_snapshots in');
1793:
1794: BIS_COLLECTION_UTILITIES.put_line(' ');
1795: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_shortfall_snapshots for constrained plans.');
1796: FII_UTIL.Start_Timer;
1797:
1798: INSERT

Line 1795: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_shortfall_snapshots for constrained plans.');

1791: FII_UTIL.Stop_Timer;
1792: FII_UTIL.Print_Timer('Inserted '|| sql%rowcount ||' rows into isc_dbi_shortfall_snapshots in');
1793:
1794: BIS_COLLECTION_UTILITIES.put_line(' ');
1795: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_shortfall_snapshots for constrained plans.');
1796: FII_UTIL.Start_Timer;
1797:
1798: INSERT
1799: INTO isc_dbi_shortfall_snapshots F(

Line 1915: BIS_COLLECTION_UTILITIES.put_line(' ');

1911: -- ------------------------------------
1912: -- Collect ISC_DBI_INV_DETAIL_SNAPSHOTS
1913: -- ------------------------------------
1914:
1915: BIS_COLLECTION_UTILITIES.put_line(' ');
1916: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_inv_detail_snapshots.');
1917: FII_UTIL.Start_Timer;
1918:
1919: -- item level only

Line 1916: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_inv_detail_snapshots.');

1912: -- Collect ISC_DBI_INV_DETAIL_SNAPSHOTS
1913: -- ------------------------------------
1914:
1915: BIS_COLLECTION_UTILITIES.put_line(' ');
1916: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_inv_detail_snapshots.');
1917: FII_UTIL.Start_Timer;
1918:
1919: -- item level only
1920:

Line 2041: BIS_COLLECTION_UTILITIES.put_line(' ');

2037: -- ----------------------------------
2038: -- Collect ISC_DBI_RES_SUM_SNAPSHOTS
2039: -- ----------------------------------
2040:
2041: BIS_COLLECTION_UTILITIES.put_line(' ');
2042: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_res_sum_snapshots.');
2043: FII_UTIL.Start_Timer;
2044:
2045: INSERT /*+ APPEND PARALLEL(F) */

Line 2042: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_res_sum_snapshots.');

2038: -- Collect ISC_DBI_RES_SUM_SNAPSHOTS
2039: -- ----------------------------------
2040:
2041: BIS_COLLECTION_UTILITIES.put_line(' ');
2042: BIS_COLLECTION_UTILITIES.put_line('Begin to insert data into isc_dbi_res_sum_snapshots.');
2043: FII_UTIL.Start_Timer;
2044:
2045: INSERT /*+ APPEND PARALLEL(F) */
2046: INTO isc_dbi_res_sum_snapshots F(

Line 2116: BIS_COLLECTION_UTILITIES.Put_Line('Purging snapshot '|| l_snapshot_id);

2112: FETCH List_of_Snapshots INTO l_snapshot_id;
2113:
2114: IF List_of_Snapshots%ROWCOUNT <> 0 THEN
2115: WHILE List_of_Snapshots%FOUND LOOP
2116: BIS_COLLECTION_UTILITIES.Put_Line('Purging snapshot '|| l_snapshot_id);
2117: IF (drop_snapshots(l_snapshot_id) = -1) THEN RETURN(-1); END IF;
2118: FETCH List_of_Snapshots INTO l_snapshot_id;
2119: END LOOP;
2120: END IF;

Line 2158: BIS_COLLECTION_UTILITIES.Put_Line(' ');

2154: -- --------------------------------------------
2155: -- Update the last and next collection date
2156: -- --------------------------------------------
2157:
2158: BIS_COLLECTION_UTILITIES.Put_Line(' ');
2159: BIS_COLLECTION_UTILITIES.put_line('Begin to update the collection information.');
2160: FII_UTIL.Start_Timer;
2161:
2162: DELETE from isc_dbi_plan_schedules

Line 2159: BIS_COLLECTION_UTILITIES.put_line('Begin to update the collection information.');

2155: -- Update the last and next collection date
2156: -- --------------------------------------------
2157:
2158: BIS_COLLECTION_UTILITIES.Put_Line(' ');
2159: BIS_COLLECTION_UTILITIES.put_line('Begin to update the collection information.');
2160: FII_UTIL.Start_Timer;
2161:
2162: DELETE from isc_dbi_plan_schedules
2163: WHERE frequency = 'ONCE'

Line 2181: BIS_COLLECTION_UTILITIES.Put_Line('Populating the next collection date for plan '|| l_plan_name);

2177:
2178: IF Plan_List%ROWCOUNT <> 0 THEN
2179: WHILE Plan_List%FOUND LOOP
2180: BEGIN
2181: BIS_COLLECTION_UTILITIES.Put_Line('Populating the next collection date for plan '|| l_plan_name);
2182: l_date := ISC_DBI_PLAN_SETUP_UTIL_PKG.get_next_collection_date(l_plan_name);
2183: UPDATE isc_dbi_plan_schedules
2184: SET next_collection_date = l_date
2185: WHERE plan_name = l_plan_name;

Line 2188: BIS_COLLECTION_UTILITIES.put_line('Plan ' || l_plan_name || ' has already been deleted from the setup form.');

2184: SET next_collection_date = l_date
2185: WHERE plan_name = l_plan_name;
2186: EXCEPTION
2187: WHEN no_data_found THEN
2188: BIS_COLLECTION_UTILITIES.put_line('Plan ' || l_plan_name || ' has already been deleted from the setup form.');
2189: END;
2190: FETCH Plan_List INTO l_plan_name;
2191: END LOOP;
2192: END IF;

Line 2203: BIS_COLLECTION_UTILITIES.Put_Line(' ');

2199: -- WHERE plan_name IN (select plan_name from isc_dbi_tmp_plans tmp WHERE bitand(tmp.plan_usage, 2) = 2);
2200:
2201: FII_UTIL.Stop_Timer;
2202: FII_UTIL.Print_Timer('Updated the setup tables in');
2203: BIS_COLLECTION_UTILITIES.Put_Line(' ');
2204:
2205: /*
2206: -- ------------------------
2207: -- Delete ISC_DBI_TMP_PLANS

Line 2210: BIS_COLLECTION_UTILITIES.put_line(' ');

2206: -- ------------------------
2207: -- Delete ISC_DBI_TMP_PLANS
2208: -- ------------------------
2209:
2210: BIS_COLLECTION_UTILITIES.put_line(' ');
2211: BIS_COLLECTION_UTILITIES.put_line('Truncating the temp table');
2212: FII_UTIL.Start_Timer;
2213:
2214: l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_tmp_plans';

Line 2211: BIS_COLLECTION_UTILITIES.put_line('Truncating the temp table');

2207: -- Delete ISC_DBI_TMP_PLANS
2208: -- ------------------------
2209:
2210: BIS_COLLECTION_UTILITIES.put_line(' ');
2211: BIS_COLLECTION_UTILITIES.put_line('Truncating the temp table');
2212: FII_UTIL.Start_Timer;
2213:
2214: l_trunc_stmt := 'truncate table ' || g_isc_schema ||'.isc_dbi_tmp_plans';
2215: EXECUTE IMMEDIATE l_trunc_stmt;

Line 2226: BIS_COLLECTION_UTILITIES.WRAPUP(TRUE, 0, NULL, NULL, NULL);

2222: -- Wrapup to commit and insert messages into logs
2223: -- ----------------------------------------------
2224: COMMIT;
2225:
2226: BIS_COLLECTION_UTILITIES.WRAPUP(TRUE, 0, NULL, NULL, NULL);
2227: RETURN(1);
2228:
2229: EXCEPTION
2230: WHEN OTHERS THEN

Line 2262: IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_PLAN_F')) THEN

2258: BEGIN
2259: errbuf := NULL;
2260: retcode := '0';
2261:
2262: IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_PLAN_F')) THEN
2263: RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
2264: return;
2265: END IF;
2266:

Line 2284: BIS_COLLECTION_UTILITIES.put_line(' ');

2280: -- --------------------------------------------------------
2281: -- Truncate all the snapshot tables before the initial load
2282: -- --------------------------------------------------------
2283:
2284: BIS_COLLECTION_UTILITIES.put_line(' ');
2285: BIS_COLLECTION_UTILITIES.put_line('Begin to truncate and drop all the snapshots');
2286:
2287: -- Bug 4939001: Drop all partitions during initial load
2288: --

Line 2285: BIS_COLLECTION_UTILITIES.put_line('Begin to truncate and drop all the snapshots');

2281: -- Truncate all the snapshot tables before the initial load
2282: -- --------------------------------------------------------
2283:
2284: BIS_COLLECTION_UTILITIES.put_line(' ');
2285: BIS_COLLECTION_UTILITIES.put_line('Begin to truncate and drop all the snapshots');
2286:
2287: -- Bug 4939001: Drop all partitions during initial load
2288: --
2289: -- FOR j in 1..g_large_snapshots.last LOOP

Line 2318: BIS_COLLECTION_UTILITIES.Put_Line(' ');

2314: END LOOP;
2315:
2316: FII_UTIL.Stop_Timer;
2317: FII_UTIL.Print_Timer('Truncated the snapshot tables in');
2318: BIS_COLLECTION_UTILITIES.Put_Line(' ');
2319:
2320: -- --------------------------------------------
2321: -- Insert data into the snapshot tables
2322: -- --------------------------------------------

Line 2324: BIS_COLLECTION_UTILITIES.put_line(' ');

2320: -- --------------------------------------------
2321: -- Insert data into the snapshot tables
2322: -- --------------------------------------------
2323:
2324: BIS_COLLECTION_UTILITIES.put_line(' ');
2325: BIS_COLLECTION_UTILITIES.put_line('Begin to load the snapshot tables');
2326:
2327: IF (load_snapshots = -1) THEN
2328: RAISE l_failure;

Line 2325: BIS_COLLECTION_UTILITIES.put_line('Begin to load the snapshot tables');

2321: -- Insert data into the snapshot tables
2322: -- --------------------------------------------
2323:
2324: BIS_COLLECTION_UTILITIES.put_line(' ');
2325: BIS_COLLECTION_UTILITIES.put_line('Begin to load the snapshot tables');
2326:
2327: IF (load_snapshots = -1) THEN
2328: RAISE l_failure;
2329: END IF;

Line 2342: BIS_COLLECTION_UTILITIES.put_line(' ');

2338: EXCEPTION
2339:
2340: WHEN L_FAILURE THEN
2341: ROLLBACK;
2342: BIS_COLLECTION_UTILITIES.put_line(' ');
2343: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
2344: retcode := -1;
2345: errbuf := g_errbuf;
2346:

Line 2343: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);

2339:
2340: WHEN L_FAILURE THEN
2341: ROLLBACK;
2342: BIS_COLLECTION_UTILITIES.put_line(' ');
2343: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
2344: retcode := -1;
2345: errbuf := g_errbuf;
2346:
2347: BIS_COLLECTION_UTILITIES.WRAPUP(

Line 2347: BIS_COLLECTION_UTILITIES.WRAPUP(

2343: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
2344: retcode := -1;
2345: errbuf := g_errbuf;
2346:
2347: BIS_COLLECTION_UTILITIES.WRAPUP(
2348: FALSE,
2349: g_row_count,
2350: g_errbuf,
2351: NULL,

Line 2360: BIS_COLLECTION_UTILITIES.WRAPUP(

2356: ROLLBACK;
2357: retcode := -1;
2358: errbuf := g_errbuf;
2359:
2360: BIS_COLLECTION_UTILITIES.WRAPUP(
2361: FALSE,
2362: g_row_count,
2363: g_errbuf,
2364: NULL,

Line 2371: BIS_COLLECTION_UTILITIES.put_line(' ');

2367:
2368: WHEN OTHERS THEN
2369: ROLLBACK;
2370: g_errbuf := sqlerrm ||' - '||sqlcode;
2371: BIS_COLLECTION_UTILITIES.put_line(' ');
2372: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
2373: retcode := -1;
2374: errbuf := g_errbuf;
2375:

Line 2372: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);

2368: WHEN OTHERS THEN
2369: ROLLBACK;
2370: g_errbuf := sqlerrm ||' - '||sqlcode;
2371: BIS_COLLECTION_UTILITIES.put_line(' ');
2372: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
2373: retcode := -1;
2374: errbuf := g_errbuf;
2375:
2376: BIS_COLLECTION_UTILITIES.WRAPUP(

Line 2376: BIS_COLLECTION_UTILITIES.WRAPUP(

2372: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
2373: retcode := -1;
2374: errbuf := g_errbuf;
2375:
2376: BIS_COLLECTION_UTILITIES.WRAPUP(
2377: FALSE,
2378: g_row_count,
2379: g_errbuf,
2380: NULL,

Line 2398: IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_PLAN_F_INC')) THEN

2394: BEGIN
2395: errbuf := NULL;
2396: retcode := '0';
2397:
2398: IF (NOT BIS_COLLECTION_UTILITIES.setup('ISC_DBI_PLAN_F_INC')) THEN
2399: RAISE_APPLICATION_ERROR (-20000,'Error in SETUP: ' || errbuf);
2400: return;
2401: END IF;
2402:

Line 2420: BIS_COLLECTION_UTILITIES.Put_Line(' ');

2416: -- --------------------------------------------
2417: -- Purge Snapshot Tables
2418: -- --------------------------------------------
2419:
2420: BIS_COLLECTION_UTILITIES.Put_Line(' ');
2421: BIS_COLLECTION_UTILITIES.put_line('Begin to purge the obsolete snapshots ');
2422: FII_UTIL.Start_Timer;
2423:
2424: l_purge := PURGE_SNAPSHOTS;

Line 2421: BIS_COLLECTION_UTILITIES.put_line('Begin to purge the obsolete snapshots ');

2417: -- Purge Snapshot Tables
2418: -- --------------------------------------------
2419:
2420: BIS_COLLECTION_UTILITIES.Put_Line(' ');
2421: BIS_COLLECTION_UTILITIES.put_line('Begin to purge the obsolete snapshots ');
2422: FII_UTIL.Start_Timer;
2423:
2424: l_purge := PURGE_SNAPSHOTS;
2425:

Line 2432: BIS_COLLECTION_UTILITIES.Put_Line(' ');

2428: END IF;
2429:
2430: FII_UTIL.Stop_Timer;
2431: FII_UTIL.Print_Timer('Purged '|| l_purge ||' snapshots in');
2432: BIS_COLLECTION_UTILITIES.Put_Line(' ');
2433:
2434: -- --------------------------------------------
2435: -- Insert data into the snapshot tables
2436: -- --------------------------------------------

Line 2438: BIS_COLLECTION_UTILITIES.put_line(' ');

2434: -- --------------------------------------------
2435: -- Insert data into the snapshot tables
2436: -- --------------------------------------------
2437:
2438: BIS_COLLECTION_UTILITIES.put_line(' ');
2439: BIS_COLLECTION_UTILITIES.put_line('Begin to load the snapshot tables');
2440:
2441: IF (load_snapshots = -1) THEN
2442: RAISE l_failure;

Line 2439: BIS_COLLECTION_UTILITIES.put_line('Begin to load the snapshot tables');

2435: -- Insert data into the snapshot tables
2436: -- --------------------------------------------
2437:
2438: BIS_COLLECTION_UTILITIES.put_line(' ');
2439: BIS_COLLECTION_UTILITIES.put_line('Begin to load the snapshot tables');
2440:
2441: IF (load_snapshots = -1) THEN
2442: RAISE l_failure;
2443: END IF;

Line 2456: BIS_COLLECTION_UTILITIES.put_line(' ');

2452: EXCEPTION
2453:
2454: WHEN L_FAILURE THEN
2455: ROLLBACK;
2456: BIS_COLLECTION_UTILITIES.put_line(' ');
2457: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
2458: retcode := -1;
2459: errbuf := g_errbuf;
2460:

Line 2457: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);

2453:
2454: WHEN L_FAILURE THEN
2455: ROLLBACK;
2456: BIS_COLLECTION_UTILITIES.put_line(' ');
2457: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
2458: retcode := -1;
2459: errbuf := g_errbuf;
2460:
2461: BIS_COLLECTION_UTILITIES.WRAPUP(

Line 2461: BIS_COLLECTION_UTILITIES.WRAPUP(

2457: BIS_COLLECTION_UTILITIES.put_line(g_errbuf);
2458: retcode := -1;
2459: errbuf := g_errbuf;
2460:
2461: BIS_COLLECTION_UTILITIES.WRAPUP(
2462: FALSE,
2463: g_row_count,
2464: g_errbuf,
2465: NULL,

Line 2474: BIS_COLLECTION_UTILITIES.WRAPUP(

2470: ROLLBACK;
2471: retcode := -1;
2472: errbuf := g_errbuf;
2473:
2474: BIS_COLLECTION_UTILITIES.WRAPUP(
2475: FALSE,
2476: g_row_count,
2477: g_errbuf,
2478: NULL,

Line 2485: BIS_COLLECTION_UTILITIES.put_line(' ');

2481:
2482: WHEN OTHERS THEN
2483: ROLLBACK;
2484: g_errbuf := sqlerrm ||' - '||sqlcode;
2485: BIS_COLLECTION_UTILITIES.put_line(' ');
2486: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
2487: retcode := -1;
2488: errbuf := g_errbuf;
2489:

Line 2486: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);

2482: WHEN OTHERS THEN
2483: ROLLBACK;
2484: g_errbuf := sqlerrm ||' - '||sqlcode;
2485: BIS_COLLECTION_UTILITIES.put_line(' ');
2486: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
2487: retcode := -1;
2488: errbuf := g_errbuf;
2489:
2490: BIS_COLLECTION_UTILITIES.WRAPUP(

Line 2490: BIS_COLLECTION_UTILITIES.WRAPUP(

2486: BIS_COLLECTION_UTILITIES.put_line('Other errors : '|| g_errbuf);
2487: retcode := -1;
2488: errbuf := g_errbuf;
2489:
2490: BIS_COLLECTION_UTILITIES.WRAPUP(
2491: FALSE,
2492: g_row_count,
2493: g_errbuf,
2494: NULL,