[Home] [Help]
498: -- denomalized columns
499: from
500: ( --
501: -- this query selects the current state of all tasks based on the
502: -- data that was collected into isc_fs_tasks_f.
503: -- the row from this query will be the marker
504: -- 1. for the initial row for tasks created after GSD or
505: -- 2. for the beginning row tasks created before GSD that are
506: -- included in the beginning backlog
537: , t.customer_id
538: , t.planned_start_date
539: -- denomalized columns
540: from
541: isc_fs_tasks_f t
542: where
543: t.source_object_type_code = 'SR'
544: -- don't restrict to just rule of 'DISPATCH' as
545: -- could subsequently change type and we would
550: --
551: union all
552: --
553: -- this query selects all rows from the task audit table
554: -- for tasks that were collected into isc_fs_tasks_f.
555: --
556: -- only include audits created between GSD and the end date
557: -- of the load to isc_fs_tasks_f.
558: --
553: -- this query selects all rows from the task audit table
554: -- for tasks that were collected into isc_fs_tasks_f.
555: --
556: -- only include audits created between GSD and the end date
557: -- of the load to isc_fs_tasks_f.
558: --
559: -- the first row for an audit may ne consumed twice, once
560: -- for the initial values for the task and again for the
561: -- new values (the change).
586: , t.customer_id
587: , t.planned_start_date
588: -- denomalized columns
589: from
590: isc_fs_tasks_f t
591: , jtf_task_audits_b a
592: where
593: t.task_id = a.task_id
594: and t.source_object_type_code = 'SR'
702: , inventory_item_id
703: , inv_organization_id
704: , customer_id
705: , planned_start_date
706: from isc_fs_tasks_f
707: where last_update_date >= l_collect_from_date;
708:
709: begin
710:
819: , customer_id
820: , planned_start_date
821: -- denomalized columns
822: )
823: /* These hints are given assuming the num of rows from ISC_FS_TASKS_F with
824: the last_update_date filter would be in the range 3000 - 6000 */
825: select /*+ ordered use_nl(s_new,s_old) */
826: a.task_id
827: , a.task_audit_id
898: -- denomalized columns
899: from
900: ( --
901: -- this selects audits (including initial creation) for all
902: -- tasks updated since last collection based on isc_fs_tasks_f
903: select
904: task_id
905: , task_audit_id
906: , audit_date
939: -- denomalized columns
940: from
941: ( --
942: -- this query selects the current state of all tasks based on the
943: -- data that was collected into isc_fs_tasks_f.
944: -- the row from this query will be the marker for the initial row
945: -- for tasks created since last collection
946: --
947: select
965: , t.customer_id
966: , t.planned_start_date
967: -- denomalized columns
968: from
969: isc_fs_tasks_f t
970: where
971: t.last_update_date >= l_collect_from_date
972: and t.task_creation_date >= l_collect_from_date
973: and nvl(t.task_split_flag,'N') in ('N','M')
975: union all
976: --
977: -- this query selects rows from the task audit table created between
978: -- last collection of activty/backlog and last collection of
979: -- isc_fs_tasks_f for tasks that were updated in isc_fs_tasks_f
980: -- since last collection of activty/backlog.
981: --
982: select /*+ ordered use_nl(A) */
983: a.task_id
1000: , t.customer_id
1001: , t.planned_start_date
1002: -- denomalized columns
1003: from
1004: isc_fs_tasks_f t
1005: , jtf_task_audits_b a
1006: where
1007: t.task_id = a.task_id
1008: and t.last_update_date >= l_collect_from_date
1097: , t.planned_start_date
1098: -- denomalized columns
1099: from
1100: isc_fs_task_backlog_f b
1101: , isc_fs_tasks_f t
1102: where
1103: b.backlog_date_to = g_max_date
1104: and b.task_id = t.task_id;
1105:
1132: --
1133: -- for each task in the audit table we find the "last" row, if that task is open
1134: -- and the date of that row is not the same as the collection, we compare
1135: -- backlog_status_code of that row with backlog_status_code calculated based
1136: -- on isc_fs_tasks_f. if they differ we insert a "dummy" audit row.
1137: --
1138: -- the most likely (but not highly likely as above) reason that they might differ
1139: -- is that at the time of the last audit row the backlog_status_code was IN PLANNING
1140: -- bacause there were no assignments, otherwise it would have been WORKING etc.
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
1237: where
1238: b.rnk = 1
1239: and b.status_flag = 'O'
1308: -- ---------------------------------------------- --
1309:
1310: bis_collection_utilities_log( 'Updating changes to denormalized data for existing rows', 1 );
1311:
1312: -- pick up changes in denormalized columns from isc_fs_tasks_f
1313: -- for existing activity and end dated backlog
1314: l_stmt_id := 92;
1315: l_rowcount := 0;
1316: l_temp_rowcount := 0;