[Home] [Help]
308: -- truncate the events table
309: l_stmt_id := 40;
310: if truncate_table
311: ( l_isc_schema
312: , 'ISC_FS_EVENTS'
313: , l_error_message ) <> 0 then
314: logger( l_proc_name, l_stmt_id, l_error_message );
315: raise l_exception;
316: end if;
1466: logger( l_proc_name, l_stmt_id, l_error_message );
1467: raise l_exception;
1468: end if;
1469:
1470: -- truncate the staging table isc_fs_events_stg
1471: l_stmt_id := 40;
1472: if truncate_table
1473: ( l_isc_schema
1474: , 'ISC_FS_EVENTS_STG'
1470: -- truncate the staging table isc_fs_events_stg
1471: l_stmt_id := 40;
1472: if truncate_table
1473: ( l_isc_schema
1474: , 'ISC_FS_EVENTS_STG'
1475: , l_error_message ) <> 0 then
1476: logger( l_proc_name, l_stmt_id, l_error_message );
1477: raise l_exception;
1478: end if;
1480: bis_collection_utilities_log( 'Staging table truncated', 1 );
1481:
1482: -- insert into staging table from events table
1483: l_stmt_id := 40;
1484: insert into isc_fs_events_stg
1485: ( source
1486: , event_rowid
1487: , task_id
1488: , source_object_type_code
1496: , source_object_type_code
1497: , source_object_id
1498: , task_assignment_id
1499: from
1500: isc_fs_events;
1501:
1502: l_rowcount := sql%rowcount;
1503: commit;
1504:
1505: bis_collection_utilities_log( l_rowcount || ' rows inserted into staging table from events', 1 );
1506:
1507: -- insert into staging table from party merge events table
1508: l_stmt_id := 50;
1509: insert into isc_fs_events_stg
1510: ( source
1511: , event_rowid
1512: , task_id
1513: , source_object_type_code
1530: -- gather stats for staging table
1531: l_stmt_id := 60;
1532: if gather_statistics
1533: ( l_isc_schema
1534: , 'ISC_FS_EVENTS_STG'
1535: , l_error_message ) <> 0 then
1536: logger( l_proc_name, l_stmt_id, l_error_message );
1537: raise l_exception;
1538: end if;
1582: , ( select /*+ NO_MERGE */ distinct
1583: task_id
1584: , task_assignment_id
1585: from
1586: isc_fs_events_stg
1587: where task_assignment_id is not null
1588: and source_object_type_code = 'SR'
1589: ) e
1590: , jtf_rs_default_groups dga
2237: , mtl_uom_conversions t_peff
2238: , ( select /*+ NO_MERGE */ distinct
2239: source_object_id
2240: from
2241: isc_fs_events_stg
2242: where
2243: source_object_type_code = 'SR'
2244: ) e
2245: , cs_incidents_all_b i
2529: jtf_tasks_b t
2530: , jtf_task_assignments ta
2531: , ( select /*+ NO_MERGE */ distinct
2532: task_id
2533: from isc_fs_events_stg
2534: where source_object_type_code = 'TASK'
2535: ) e
2536: , jtf_rs_default_groups dg
2537: , cac_sr_object_capacity oc
2617: l_rowcount := l_rowcount + l_temp_rowcount;
2618:
2619: -- delete processed rows from events table
2620: l_stmt_id := 100;
2621: delete from isc_fs_events
2622: where rowid in ( select event_rowid from isc_fs_events_stg where source = 1 );
2623:
2624: l_temp_rowcount := sql%rowcount;
2625:
2618:
2619: -- delete processed rows from events table
2620: l_stmt_id := 100;
2621: delete from isc_fs_events
2622: where rowid in ( select event_rowid from isc_fs_events_stg where source = 1 );
2623:
2624: l_temp_rowcount := sql%rowcount;
2625:
2626: bis_collection_utilities_log( l_temp_rowcount || ' rows deleted from events table', 1 );
2627:
2628: -- delete processed rows from party merge events table
2629: l_stmt_id := 110;
2630: delete from isc_fs_party_merge_events
2631: where rowid in ( select event_rowid from isc_fs_events_stg where source = 2 );
2632:
2633: l_temp_rowcount := sql%rowcount;
2634:
2635: bis_collection_utilities_log( l_temp_rowcount || ' rows deleted from party merge events table', 1 );
2675: -- house keeping -- cleanup staging table
2676: l_stmt_id := 130;
2677: if truncate_table
2678: ( l_isc_schema
2679: , 'ISC_FS_EVENTS_STG'
2680: , l_error_message ) <> 0 then
2681: logger( l_proc_name, l_stmt_id, l_error_message );
2682: raise l_exception;
2683: end if;