[Home] [Help]
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';
128: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_FIH_LOG';
129:
130: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_DEL_DETAILS';
131: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_DEL_LEGS';
779: l_total := 0;
780:
781: FII_UTIL.Start_Timer;
782:
783: INSERT INTO isc_dbi_tmp_wts_log (STOP_ID, LOG_ROWID, DML_TYPE, LAST_UPDATE_DATE)
784: SELECT stop_id, rowid LOG_ROWID, dml_type, last_update_date
785: FROM isc_dbi_wts_change_log;
786:
787: FII_UTIL.Stop_Timer;
784: SELECT stop_id, rowid LOG_ROWID, dml_type, last_update_date
785: FROM isc_dbi_wts_change_log;
786:
787: FII_UTIL.Stop_Timer;
788: FII_UTIL.Print_Timer('Inserted '|| sql%rowcount || ' rows into ISC_DBI_TMP_WTS_LOG');
789:
790: COMMIT;
791:
792: FND_STATS.GATHER_TABLE_STATS(OWNNAME => g_isc_schema,
789:
790: COMMIT;
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:
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
800: FROM isc_dbi_tmp_wts_log log,
801: isc_dbi_del_legs_f idl
802: WHERE (log.stop_id = idl.pick_up_stop_id or log.stop_id = idl.drop_off_stop_id)
803: AND NOT EXISTS (select '1' from wsh_delivery_legs wdl where wdl.delivery_leg_id = idl.delivery_leg_id));
804:
809: FII_UTIL.Start_Timer;
810:
811: -- DELETE FROM isc_dbi_trip_stops_f
812: -- WHERE stop_id IN (SELECT DISTINCT wts.stop_id
813: -- FROM isc_dbi_tmp_wts_log log,
814: -- isc_dbi_
815: -- WHERE dml_type = 'DELETE');
816:
817: DELETE FROM isc_dbi_trip_stops_f
815: -- WHERE dml_type = 'DELETE');
816:
817: DELETE FROM isc_dbi_trip_stops_f
818: WHERE stop_id IN (SELECT DISTINCT log.stop_id
819: FROM isc_dbi_tmp_wts_log log
820: WHERE NOT EXISTS (select '1' from wsh_trip_stops wts, wsh_trips wt
821: where log.stop_id = wts.stop_id
822: and wts.trip_id = wt.trip_id
823: and wt.status_code IN ('IT', 'CL')));
861: wts.stop_sequence_number STOP_SEQUENCE_NUMBER,
862: wt.trip_id TRIP_ID,
863: max(wts.stop_sequence_number) over (partition by wt.trip_id) ULTIMATE_STOP_SEQUENCE_NUMBER
864: FROM (select /*+ no_merge index(tr) */ distinct tr.trip_id
865: from isc_dbi_tmp_wts_log tmp, wsh_trip_stops tr
866: where tmp.stop_id = tr.stop_id) log,
867: wsh_trips wt,
868: wsh_trip_stops wts
869: WHERE log.trip_id = wt.trip_id
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';
3116: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_FIH_LOG';
3117:
3118: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_DEL_DETAILS';
3119: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_DEL_LEGS';
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);
3600: -- AND last_update_date < g_incre_start_date;
3601:
3602: FII_UTIL.Stop_Timer;
3603: FII_UTIL.Print_Timer('Deleted ' || sql%rowcount || ' rows from ISC_DBI_WTS_CHANGE_LOG in');