[Home] [Help]
771: -- truncate the staging table
772: l_stmt_id := 40;
773: if truncate_table
774: ( l_isc_schema
775: , 'ISC_FS_TASK_ACT_BAC_STG'
776: , l_error_message ) <> 0 then
777: logger( l_proc_name, l_stmt_id, l_error_message );
778: raise l_exception;
779: end if;
785: --
786: -- insert rows based on tasks created or tasks updated
787: --
788: l_stmt_id := 50;
789: insert into isc_fs_task_act_bac_stg
790: ( task_id
791: , task_audit_id
792: , status_flag
793: , audit_date
1032: -- last row for day for the same task to determine if
1033: -- it needs to be closed off
1034: --
1035: l_stmt_id := 60;
1036: insert into isc_fs_task_act_bac_stg
1037: ( task_id
1038: , task_audit_id
1039: , backlog_status_code
1040: , status_flag
1112: -- gather stats on staging table
1113: l_stmt_id := 70;
1114: if gather_statistics
1115: ( l_isc_schema
1116: , 'ISC_FS_TASK_ACT_BAC_STG'
1117: , l_error_message ) <> 0 then
1118: logger( l_proc_name, l_stmt_id, l_error_message );
1119: raise l_exception;
1120: end if;
1144:
1145: bis_collection_utilities_log( 'Inserting closing backlog status into staging table', 1 );
1146:
1147: l_stmt_id := 80;
1148: insert into isc_fs_task_act_bac_stg
1149: ( task_id
1150: , task_audit_id
1151: , backlog_status_code
1152: , status_flag
1175: , customer_id
1176: , planned_start_date
1177: -- denomalized columns
1178: )
1179: /* If the volume of the table ISC_FS_TASK_ACT_BAC_STG is going to be high, create
1180: an index on LAST_ROW_FOR_DAY_FLAG with a histogram */
1181: select /*+ ordered use_nl(T,S) */
1182: t.task_id
1183: , -0.1 task_audit_id
1228: , backlog_status_code
1229: , status_flag
1230: , rank() over(partition by task_id order by audit_date desc, task_audit_id desc) rnk
1231: from
1232: isc_fs_task_act_bac_stg
1233: where last_row_for_day_flag = 'Y'
1234: ) b
1235: , isc_fs_tasks_f t
1236: , jtf_task_statuses_b s
1265: bis_collection_utilities_log( 'Hiding ''duplicate'' rows from backlog query', 1 );
1266:
1267: -- hide 'duplicate' rows from backlog query
1268: l_stmt_id := 90;
1269: update isc_fs_task_act_bac_stg
1270: set status_flag = lower(status_flag)
1271: , last_updated_by = g_user_id
1272: , last_update_date = sysdate
1273: , last_update_login = g_login_id
1287: task_id
1288: , audit_date
1289: , task_audit_id) prev_conc_key
1290: from
1291: isc_fs_task_act_bac_stg
1292: where
1293: last_row_for_day_flag = 'Y'
1294: )
1295: where conc_key = prev_conc_key
1423: over( partition by task_id order by audit_date, task_audit_id ) lead_audit_date
1424: , backlog_status_code
1425: , status_flag
1426: from
1427: isc_fs_task_act_bac_stg
1428: where
1429: last_row_for_day_flag = 'Y'
1430: and status_flag in ('O','C')
1431: )
1523: , inv_organization_id
1524: , customer_id
1525: -- denomalized columns
1526: from
1527: isc_fs_task_act_bac_stg
1528: where
1529: trunc(audit_date) >= g_global_start_date
1530: and 1 in ( first_opened
1531: , reopened
1629: , customer_id
1630: , planned_start_date
1631: -- denomalized columns
1632: from
1633: isc_fs_task_act_bac_stg
1634: where
1635: status_flag in ('O','C')
1636: and last_row_for_day_flag = 'Y';
1637:
1646: -- house keeping -- cleanup staging table
1647: l_stmt_id := 140;
1648: if truncate_table
1649: ( l_isc_schema
1650: , 'ISC_FS_TASK_ACT_BAC_STG'
1651: , l_error_message ) <> 0 then
1652: logger( l_proc_name, l_stmt_id, l_error_message );
1653: raise l_exception;
1654: end if;