[Home] [Help]
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
1061: FROM isc_dbi_tmp_fih_log log
1062: WHERE NOT EXISTS (select '1' from fte_invoice_headers fih
1063: where fih.invoice_header_id = log.invoice_header_id
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,
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,
2391: CARRIER_ID,
2392: MODE_OF_TRANSPORT,
2393: ORG_ID,
2458: AND v.conversion_type_code = curr.conversion_type_code;
2459:
2460: l_invoice_count := sql%rowcount;
2461: FII_UTIL.Stop_Timer;
2462: FII_UTIL.Print_Timer('Inserted '|| l_invoice_count ||' rows into isc_dbi_fte_invoices_f in');
2463:
2464: COMMIT;
2465: RETURN(l_detail_count + l_leg_count + l_stop_count + l_invoice_count);
2466:
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
2954: USING
2955: (select new.*
2956: from (select tmp.invoice_header_id INVOICE_HEADER_ID,
2957: tmp.carrier_id CARRIER_ID,
2984: and tmp.wh_currency_code = curr.wh_currency_code
2985: and tmp.conversion_date = curr.conversion_date
2986: and tmp.conversion_type_code = curr.conversion_type_code
2987: and tmp.batch_id = v_batch_id) new,
2988: isc_dbi_fte_invoices_f old
2989: where new.invoice_header_id = old.invoice_header_id(+)
2990: and (old.invoice_header_id is null
2991: or new.carrier_id <> old.carrier_id
2992: or new.mode_of_transport <> old.mode_of_transport
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';
3223: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TRIP_STOPS_F';
3224: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_FTE_INVOICES_F';
3225: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_FTE_PARAMETERS';
3226:
3227: FII_UTIL.Stop_Timer;
3228: FII_UTIL.Print_Timer('Truncated the fact tables in');
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';
3271: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TRIP_STOPS_F';
3272: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_FTE_INVOICES_F';
3273: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_FTE_PARAMETERS';
3274:
3275: FII_UTIL.Stop_Timer;
3276: FII_UTIL.Print_Timer('Truncated the fact tables in');
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: