[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert /*+ append
parallel(isc_fs_task_activity_f)
parallel(isc_fs_task_backlog_f)
*/
ALL
when 1 in (first_opened, reopened, closed) then
into isc_fs_task_activity_f
( task_id
, task_audit_id
, activity_date
, first_opened
, reopened
, closed
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
-- denomalized columns
, source_object_type_code
, task_type_id
, task_type_rule
, deleted_flag
, act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type
, act_bac_district_id
, inventory_item_id
, inv_organization_id
, customer_id
-- denomalized columns
)
values
( task_id
, task_audit_id
, activity_date
, first_opened
, reopened
, closed
, g_user_id
, sysdate
, g_user_id
, sysdate
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
-- denomalized columns
, source_object_type_code
, task_type_id
, task_type_rule
, deleted_flag
, act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type
, act_bac_district_id
, inventory_item_id
, inv_organization_id
, customer_id
-- denomalized columns
)
when backlog_date_from is not null then
into isc_fs_task_backlog_f
( task_id
, task_audit_id
, backlog_date_from
, backlog_date_to
, backlog_status_code
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
-- denomalized columns
, source_object_type_code
, task_type_id
, task_type_rule
, deleted_flag
, act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type
, act_bac_district_id
, inventory_item_id
, inv_organization_id
, customer_id
, planned_start_date
-- denomalized columns
)
values
( task_id
, task_audit_id
, backlog_date_from
, backlog_date_to
, backlog_status_code
, g_user_id
, sysdate
, g_user_id
, sysdate
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
-- denomalized columns
, source_object_type_code
, task_type_id
, task_type_rule
, deleted_flag
, act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type
, act_bac_district_id
, inventory_item_id
, inv_organization_id
, customer_id
, planned_start_date
-- denomalized columns
)
select /*+ parallel(a)
parallel(s_new)
parallel(s_old)
parallel(e_act)
use_nl(s_new,s_old)
*/
a.task_id
, a.task_audit_id
, trunc(a.audit_date) activity_date
, decode( a.task_audit_id
, -1, 1
, null ) first_opened
, case
when a.task_audit_id < 0 then
null
when nvl(s_new.closed_flag,'N') = 'N' and
nvl(s_old.closed_flag,'N') = 'Y' then
1
else
null
end reopened
, case
when a.task_audit_id = -2 then
null
when a.task_audit_id = -1 and
nvl(s_new.closed_flag,'N') = 'Y' then
1
when nvl(s_new.closed_flag,'N') = 'Y' and
nvl(s_old.closed_flag,'N') = 'N' then
1
else
null
end closed
, case
when nvl(s_new.closed_flag,'N') = 'N' then
case
-- note: the sequence of the "when" is important, don't change it!
when nvl(s_new.schedulable_flag,'N') = 'Y' or
trunc(a.audit_date) < trunc(nvl(a.first_asgn_creation_date,g_max_date)) then
1 --'IN PLANNING'
when nvl(s_new.working_flag,'N') = 'Y' then
3 --'WORKING'
when nvl(s_new.assigned_flag,'N') = 'Y' then
2 --'ASSIGNED'
when nvl(s_new.completed_flag,'N') = 'Y' then
4 -- 'COMPLETED'
else
5 -- 'OTHER'
end
else
null
end backlog_status_code
, case
when last_row_for_day_flag = 'Y' and
nvl(s_new.closed_flag,'N') = 'N' then
trunc(a.audit_date)
else null
end backlog_date_from
, case
when last_row_for_day_flag = 'Y' and
nvl(s_new.closed_flag,'N') = 'N' then
lead(trunc(a.audit_date)-1,1,g_max_date) over(partition by task_id order by a.audit_date, a.task_audit_id)
else null
end backlog_date_to
-- denomalized columns
, source_object_type_code
, task_type_id
, task_type_rule
, deleted_flag
, act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type
, act_bac_district_id
, inventory_item_id
, inv_organization_id
, customer_id
, planned_start_date
-- denomalized columns
from
( --
-- this selects audits (including initial creation) for all
-- tasks of interest where the task was created on or after
-- GSD and all audit rows since GSD for tasks of interest
-- created before GSD
select /*+ no_merge parallel(x)
*/
task_id
, task_audit_id
, audit_date
, case
when task_audit_id < 0 then
lead(old_task_status_id,1,task_status_id)
over(partition by task_id order by audit_date, task_audit_id)
else
task_status_id
end task_status_id
, case
when task_audit_id = -1 then
-1
else
old_task_status_id
end old_task_status_id
, first_asgn_creation_date
, decode( row_number()
over(partition by task_id, trunc(audit_date) order by audit_date desc, task_audit_id desc)
, 1, 'Y'
, 'N' ) last_row_for_day_flag
-- denomalized columns
, source_object_type_code
, task_type_id
, task_type_rule
, deleted_flag
, act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type
, act_bac_district_id
, inventory_item_id
, inv_organization_id
, customer_id
, planned_start_date
-- denomalized columns
from
( --
-- this query selects the current state of all tasks based on the
-- data that was collected into isc_fs_tasks_f.
-- the row from this query will be the marker
-- 1. for the initial row for tasks created after GSD or
-- 2. for the beginning row tasks created before GSD that are
-- included in the beginning backlog
--
select /*+ parallel(t) no_merge
*/
t.task_id
, case
when t.task_creation_date < l_collect_from_date then
-2
else
-1
end task_audit_id
, case
when t.task_creation_date < l_collect_from_date then
l_collect_from_date
else
t.task_creation_date
end audit_date
, t.task_status_id old_task_status_id
, t.task_status_id
, t.first_asgn_creation_date
-- denomalized columns
, t.source_object_type_code
, t.task_type_id
, t.task_type_rule
, t.deleted_flag
, t.act_bac_assignee_id
-- R12 resource type impact
, t.act_bac_assignee_type
, t.act_bac_district_id
, t.inventory_item_id
, t.inv_organization_id
, t.customer_id
, t.planned_start_date
-- denomalized columns
from
isc_fs_tasks_f t
where
t.source_object_type_code = 'SR'
-- don't restrict to just rule of 'DISPATCH' as
-- could subsequently change type and we would
-- miss out on the initial backlog/activity
-- and t.task_type_rule = 'DISPATCH'
and t.task_creation_date <= l_collect_to_date
and nvl(t.task_split_flag,'N') in ('N','M')
--
union all
--
-- this query selects all rows from the task audit table
-- for tasks that were collected into isc_fs_tasks_f.
--
-- only include audits created between GSD and the end date
-- of the load to isc_fs_tasks_f.
--
-- the first row for an audit may ne consumed twice, once
-- for the initial values for the task and again for the
-- new values (the change).
--
select /*+ ordered
parallel(t)
parallel(a)
use_hash(a)
pq_distribute(a,hash,hash)
*/
a.task_id
, a.task_audit_id
, a.creation_date audit_date
, a.old_task_status_id
, a.new_task_status_id
, t.first_asgn_creation_date
-- denomalized columns
, t.source_object_type_code
, t.task_type_id
, t.task_type_rule
, t.deleted_flag
, t.act_bac_assignee_id
-- R12 resource type impact
, t.act_bac_assignee_type
, t.act_bac_district_id
, t.inventory_item_id
, t.inv_organization_id
, t.customer_id
, t.planned_start_date
-- denomalized columns
from
isc_fs_tasks_f t
, jtf_task_audits_b a
where
t.task_id = a.task_id
and t.source_object_type_code = 'SR'
-- don't restrict to just rule of 'DISPATCH' as
-- could subsequently change type and we would
-- miss out on the initial backlog/activity
-- and t.task_type_rule = 'DISPATCH'
and a.creation_date >= l_collect_from_date
and a.creation_date+0 <= l_collect_to_date
and nvl(t.task_split_flag,'N') in ('N','M')
) x
) a
, jtf_task_statuses_b s_old
, jtf_task_statuses_b s_new
where
a.task_status_id = s_new.task_status_id
and a.old_task_status_id = s_old.task_status_id(+);
bis_collection_utilities_log( l_rowcount || ' rows inserted into base summaries', 1 );
cursor c_updated is
select
task_id
, source_object_type_code
, task_type_id
, task_type_rule
, deleted_flag
, act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type
, act_bac_district_id
, inventory_item_id
, inv_organization_id
, customer_id
, planned_start_date
from isc_fs_tasks_f
where last_update_date >= l_collect_from_date;
bis_collection_utilities_log( 'Inserting Task audit history into staging table', 1 );
insert into isc_fs_task_act_bac_stg
( task_id
, task_audit_id
, status_flag
, audit_date
, first_opened
, reopened
, closed
, last_row_for_day_flag
, backlog_status_code
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
-- denomalized columns
, source_object_type_code
, task_type_id
, task_type_rule
, deleted_flag
, act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type
, act_bac_district_id
, inventory_item_id
, inv_organization_id
, customer_id
, planned_start_date
-- denomalized columns
)
/* These hints are given assuming the num of rows from ISC_FS_TASKS_F with
the last_update_date filter would be in the range 3000 - 6000 */
select /*+ ordered use_nl(s_new,s_old) */
a.task_id
, a.task_audit_id
, decode( nvl(s_new.closed_flag,'N')
, 'N', 'O'
, 'C' ) status_flag
, a.audit_date
, decode( a.task_audit_id
, -1, 1
, null ) first_opened
, case
when a.task_audit_id < 0 then
null
when nvl(s_new.closed_flag,'N') = 'N' and
nvl(s_old.closed_flag,'N') = 'Y' then
1
else
null
end reopened
, case
when a.task_audit_id = -2 then
null
when a.task_audit_id = -1 and
nvl(s_new.closed_flag,'N') = 'Y' then
1
when nvl(s_new.closed_flag,'N') = 'Y' and
nvl(s_old.closed_flag,'N') = 'N' then
1
else
null
end closed
, last_row_for_day_flag
, case
when nvl(s_new.closed_flag,'N') = 'N' then
case
-- note: the sequence of the "when" is important, don't change it!
when nvl(s_new.schedulable_flag,'N') = 'Y' or
trunc(a.audit_date) < trunc(nvl(a.first_asgn_creation_date,g_max_date)) then
1 --'IN PLANNING' -- in planning
when nvl(s_new.working_flag,'N') = 'Y' then
3 --'WORKING' -- working
when nvl(s_new.assigned_flag,'N') = 'Y' then
2 --'ASSIGNED' -- assigned
when nvl(s_new.completed_flag,'N') = 'Y' then
4 --'COMPLETED' -- completed
else
5 --'OTHER' -- others
end
else
null
end backlog_status_code
, g_user_id
, sysdate
, g_user_id
, sysdate
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
-- denomalized columns
, a.source_object_type_code
, a.task_type_id
, a.task_type_rule
, a.deleted_flag
, a.act_bac_assignee_id
-- R12 resource type impact
, a.act_bac_assignee_type
, a.act_bac_district_id
, a.inventory_item_id
, a.inv_organization_id
, a.customer_id
, a.planned_start_date
-- denomalized columns
from
( --
-- this selects audits (including initial creation) for all
-- tasks updated since last collection based on isc_fs_tasks_f
select
task_id
, task_audit_id
, audit_date
, case
when task_audit_id < 0 then
lead(old_task_status_id,1,task_status_id)
over(partition by task_id order by audit_date, task_audit_id)
else
task_status_id
end task_status_id
, case
when task_audit_id = -1 then
-1
else
old_task_status_id
end old_task_status_id
, first_asgn_creation_date
, decode( row_number()
over( partition by task_id, trunc(audit_date)
order by audit_date desc, task_audit_id desc)
, 1, 'Y'
, 'N' ) last_row_for_day_flag
-- denomalized columns
, source_object_type_code
, task_type_id
, task_type_rule
, deleted_flag
, act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type
, act_bac_district_id
, inventory_item_id
, inv_organization_id
, customer_id
, planned_start_date
-- denomalized columns
from
( --
-- this query selects the current state of all tasks based on the
-- data that was collected into isc_fs_tasks_f.
-- the row from this query will be the marker for the initial row
-- for tasks created since last collection
--
select
t.task_id
, -1 task_audit_id
, t.task_creation_date audit_date
, t.task_status_id old_task_status_id
, t.task_status_id
, t.first_asgn_creation_date
-- denomalized columns
, t.source_object_type_code
, t.task_type_id
, t.task_type_rule
, t.deleted_flag
, t.act_bac_assignee_id
-- R12 resource type impact
, t.act_bac_assignee_type
, t.act_bac_district_id
, t.inventory_item_id
, t.inv_organization_id
, t.customer_id
, t.planned_start_date
-- denomalized columns
from
isc_fs_tasks_f t
where
t.last_update_date >= l_collect_from_date
and t.task_creation_date >= l_collect_from_date
and nvl(t.task_split_flag,'N') in ('N','M')
--
union all
--
-- this query selects rows from the task audit table created between
-- last collection of activty/backlog and last collection of
-- isc_fs_tasks_f for tasks that were updated in isc_fs_tasks_f
-- since last collection of activty/backlog.
--
select /*+ ordered use_nl(A) */
a.task_id
, a.task_audit_id
, a.creation_date audit_date
, a.old_task_status_id
, a.new_task_status_id
, t.first_asgn_creation_date
-- denomalized columns
, t.source_object_type_code
, t.task_type_id
, t.task_type_rule
, t.deleted_flag
, t.act_bac_assignee_id
-- R12 resource type impact
, t.act_bac_assignee_type
, t.act_bac_district_id
, t.inventory_item_id
, t.inv_organization_id
, t.customer_id
, t.planned_start_date
-- denomalized columns
from
isc_fs_tasks_f t
, jtf_task_audits_b a
where
t.task_id = a.task_id
and t.last_update_date >= l_collect_from_date
and nvl(t.task_split_flag,'N') in ('N','M')
and a.creation_date >= l_collect_from_date
and a.creation_date <= l_collect_to_date
) x
) a
, jtf_task_statuses_b s_old
, jtf_task_statuses_b s_new
where
a.task_status_id = s_new.task_status_id
and a.old_task_status_id = s_old.task_status_id(+);
bis_collection_utilities_log( l_rowcount || ' rows inserted', 2 );
bis_collection_utilities_log( 'Inserting beginning task backlog into staging table', 1 );
insert into isc_fs_task_act_bac_stg
( task_id
, task_audit_id
, backlog_status_code
, status_flag
, audit_date
, backlog_rowid
, last_row_for_day_flag
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
-- denomalized columns
, source_object_type_code
, task_type_id
, task_type_rule
, deleted_flag
, act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type
, act_bac_district_id
, inventory_item_id
, inv_organization_id
, customer_id
, planned_start_date
-- denomalized columns
)
select
b.task_id
, b.task_audit_id
, b.backlog_status_code
, 'O'
, b.backlog_date_from
, b.rowid
, 'Y'
, g_user_id
, sysdate
, g_user_id
, sysdate
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
-- denomalized columns
, t.source_object_type_code
, t.task_type_id
, t.task_type_rule
, t.deleted_flag
, t.act_bac_assignee_id
-- R12 resource type impact
, t.act_bac_assignee_type
, t.act_bac_district_id
, t.inventory_item_id
, t.inv_organization_id
, t.customer_id
, t.planned_start_date
-- denomalized columns
from
isc_fs_task_backlog_f b
, isc_fs_tasks_f t
where
b.backlog_date_to = g_max_date
and b.task_id = t.task_id;
bis_collection_utilities_log( l_rowcount || ' rows inserted', 2 );
bis_collection_utilities_log( 'Inserting closing backlog status into staging table', 1 );
insert into isc_fs_task_act_bac_stg
( task_id
, task_audit_id
, backlog_status_code
, status_flag
, audit_date
, last_row_for_day_flag
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
-- denomalized columns
, source_object_type_code
, task_type_id
, task_type_rule
, deleted_flag
, act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type
, act_bac_district_id
, inventory_item_id
, inv_organization_id
, customer_id
, planned_start_date
-- denomalized columns
)
/* If the volume of the table ISC_FS_TASK_ACT_BAC_STG is going to be high, create
an index on LAST_ROW_FOR_DAY_FLAG with a histogram */
select /*+ ordered use_nl(T,S) */
t.task_id
, -0.1 task_audit_id
, case
-- note: the sequence of the "when" is important, don't change it!
when nvl(s.schedulable_flag,'N') = 'Y' or
trunc(l_collect_to_date) < trunc(nvl(t.first_asgn_creation_date,g_max_date)) then
1 --'IN PLANNING'
when nvl(s.working_flag,'N') = 'Y' then
3 --'WORKING'
when nvl(s.assigned_flag,'N') = 'Y' then
2 --'ASSIGNED'
when nvl(s.completed_flag,'N') = 'Y' then
4 --'COMPLETED'
else
5 --'OTHER'
end backlog_status_code
, 'O' status_flag
, l_collect_to_date audit_date
, 'Y' last_row_for_day_flag
, g_user_id
, sysdate
, g_user_id
, sysdate
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
-- denomalized columns
, t.source_object_type_code
, t.task_type_id
, t.task_type_rule
, t.deleted_flag
, t.act_bac_assignee_id
-- R12 resource type impact
, t.act_bac_assignee_type
, t.act_bac_district_id
, t.inventory_item_id
, t.inv_organization_id
, t.customer_id
, t.planned_start_date
-- denomalized columns
from
( select
task_id
, audit_date
, backlog_status_code
, status_flag
, rank() over(partition by task_id order by audit_date desc, task_audit_id desc) rnk
from
isc_fs_task_act_bac_stg
where last_row_for_day_flag = 'Y'
) b
, isc_fs_tasks_f t
, jtf_task_statuses_b s
where
b.rnk = 1
and b.status_flag = 'O'
and trunc(b.audit_date) < trunc(l_collect_to_date)
and b.task_id = t.task_id
and t.task_status_id = s.task_status_id
and nvl(s.closed_flag,'N') = 'N'
and b.backlog_status_code <> case
-- note: the sequence of the "when" is important, don't change it!
when nvl(s.schedulable_flag,'N') = 'Y' or
trunc(l_collect_to_date) < trunc(nvl(t.first_asgn_creation_date,g_max_date)) then
1 --'IN PLANNING'
when nvl(s.working_flag,'N') = 'Y' then
3 --'WORKING'
when nvl(s.assigned_flag,'N') = 'Y' then
2 --'ASSIGNED'
when nvl(s.completed_flag,'N') = 'Y' then
4 --'COMPLETED'
else
5 --'OTHER'
end;
bis_collection_utilities_log( l_rowcount || ' rows inserted', 2 );
update isc_fs_task_act_bac_stg
set status_flag = lower(status_flag)
, last_updated_by = g_user_id
, last_update_date = sysdate
, last_update_login = g_login_id
, program_id = g_program_id
, program_login_id = g_program_login_id
, program_application_id = g_program_application_id
, request_id = g_request_id
where rowid in ( select rowid
from
( select
task_id || '^' ||
backlog_status_code conc_key
, lag(task_id || '^' ||
backlog_status_code
,1,'^')
over (order by
task_id
, audit_date
, task_audit_id) prev_conc_key
from
isc_fs_task_act_bac_stg
where
last_row_for_day_flag = 'Y'
)
where conc_key = prev_conc_key
);
bis_collection_utilities_log(l_temp_rowcount || ' rows updated',2);
for i in c_updated loop
l_stmt_id := 94;
update isc_fs_task_activity_f
set
source_object_type_code = i.source_object_type_code
, task_type_id = i.task_type_id
, task_type_rule = i.task_type_rule
, deleted_flag = i.deleted_flag
, act_bac_assignee_id = i.act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type = i.act_bac_assignee_type
, act_bac_district_id = i.act_bac_district_id
, inventory_item_id = i.inventory_item_id
, inv_organization_id = i.inv_organization_id
, customer_id = i.customer_id
, last_updated_by = g_user_id
, last_update_date = sysdate
, last_update_login = g_login_id
, program_id = g_program_id
, program_login_id = g_program_login_id
, program_application_id = g_program_application_id
, request_id = g_request_id
where
task_id = i.task_id
and ( nvl(source_object_type_code,'X') <> nvl(i.source_object_type_code,'X') or -- should not be null
nvl(task_type_id,-5) <> nvl(i.task_type_id,-5) or -- should not be null
nvl(task_type_rule,'X') <> nvl(i.task_type_rule,'X') or -- may be null
nvl(deleted_flag,'X') <> nvl(i.deleted_flag,'X') or -- should not be null
nvl(act_bac_assignee_id,-5) <> nvl(i.act_bac_assignee_id,-5) or -- should not be null
-- R12 resource type impact
nvl(act_bac_assignee_type,'X') <> nvl(i.act_bac_assignee_type,'X') or -- should not be null
nvl(act_bac_district_id,-5) <> nvl(i.act_bac_district_id,-5) or -- should not be null
nvl(inventory_item_id,-5) <> nvl(i.inventory_item_id,-5) or -- should not be null
nvl(inv_organization_id,-5) <> nvl(i.inv_organization_id,-5) or -- should not be null
nvl(customer_id,-5) <> nvl(i.customer_id,-5) -- should not be null
);
update isc_fs_task_backlog_f
set
source_object_type_code = i.source_object_type_code
, task_type_id = i.task_type_id
, task_type_rule = i.task_type_rule
, deleted_flag = i.deleted_flag
, act_bac_assignee_id = i.act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type = i.act_bac_assignee_type
, act_bac_district_id = i.act_bac_district_id
, inventory_item_id = i.inventory_item_id
, inv_organization_id = i.inv_organization_id
, customer_id = i.customer_id
, planned_start_date = i.planned_start_date
, last_updated_by = g_user_id
, last_update_date = sysdate
, last_update_login = g_login_id
, program_id = g_program_id
, program_login_id = g_program_login_id
, program_application_id = g_program_application_id
, request_id = g_request_id
where
task_id = i.task_id
and ( nvl(source_object_type_code,'X') <> nvl(i.source_object_type_code,'X') or -- should not be null
nvl(task_type_id,-5) <> nvl(i.task_type_id,-5) or -- should not be null
nvl(task_type_rule,'X') <> nvl(i.task_type_rule,'X') or -- may be null
nvl(deleted_flag,'X') <> nvl(i.deleted_flag,'X') or -- should not be null
nvl(act_bac_assignee_id,-5) <> nvl(i.act_bac_assignee_id,-5) or -- should not be null
-- R12 resource type impact
nvl(act_bac_assignee_type,'X') <> nvl(i.act_bac_assignee_type,'X') or -- should not be null
nvl(act_bac_district_id,-5) <> nvl(i.act_bac_district_id,-5) or -- should not be null
nvl(inventory_item_id,-5) <> nvl(i.inventory_item_id,-5) or -- should not be null
nvl(inv_organization_id,-5) <> nvl(i.inv_organization_id,-5) or -- should not be null
nvl(customer_id,-5) <> nvl(i.customer_id,-5) or -- should not be null
nvl(planned_start_date,g_max_date) <> nvl(i.planned_start_date,g_max_date) -- may be null
);
bis_collection_utilities_log(l_rowcount || ' rows updated in activity base summary',2);
bis_collection_utilities_log(l_temp_rowcount || ' rows updated in backlog base summary',2);
select
backlog_rowid
, lead_audit_date -1
bulk collect into
l_rowid_tbl
, l_backlog_date_to
from
( select
backlog_rowid
, lead( backlog_status_code, 1, backlog_status_code )
over( partition by task_id order by audit_date, task_audit_id ) lead_backlog_status_code
, lead( status_flag, 1, status_flag )
over( partition by task_id order by audit_date, task_audit_id ) lead_status_flag
, lead( trunc(audit_date), 1, null )
over( partition by task_id order by audit_date, task_audit_id ) lead_audit_date
, backlog_status_code
, status_flag
from
isc_fs_task_act_bac_stg
where
last_row_for_day_flag = 'Y'
and status_flag in ('O','C')
)
where backlog_rowid is not null
and lead_audit_date is not null
and ( lead_backlog_status_code <> backlog_status_code or
lead_status_flag <> status_flag );
update isc_fs_task_backlog_f
set
backlog_date_to = l_backlog_date_to(i)
, last_updated_by = g_user_id
, last_update_date = sysdate
, last_update_login = g_login_id
, program_id = g_program_id
, program_login_id = g_program_login_id
, program_application_id = g_program_application_id
, request_id = g_request_id
where rowid = l_rowid_tbl(i);
bis_collection_utilities_log( l_rowid_tbl.count || ' rows updated', 3 );
bis_collection_utilities_log( 'Inserting activity', 1 );
insert into isc_fs_task_activity_f
( task_id
, task_audit_id
, activity_date
, first_opened
, reopened
, closed
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
-- denomalized columns
, source_object_type_code
, task_type_id
, task_type_rule
, deleted_flag
, act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type
, act_bac_district_id
, inventory_item_id
, inv_organization_id
, customer_id
-- denomalized columns
)
select
task_id
, task_audit_id
, trunc(audit_date)
, first_opened
, reopened
, closed
, g_user_id
, sysdate
, g_user_id
, sysdate
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
-- denomalized columns
, source_object_type_code
, task_type_id
, task_type_rule
, deleted_flag
, act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type
, act_bac_district_id
, inventory_item_id
, inv_organization_id
, customer_id
-- denomalized columns
from
isc_fs_task_act_bac_stg
where
trunc(audit_date) >= g_global_start_date
and 1 in ( first_opened
, reopened
, closed
);
bis_collection_utilities_log( l_rowcount || ' rows inserted', 2 );
bis_collection_utilities_log( 'Inserting backlog history', 1 );
insert
first
when status_flag = 'O' and
backlog_rowid is null then
into isc_fs_task_backlog_f
( task_id
, task_audit_id
, backlog_date_from
, backlog_date_to
, backlog_status_code
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
-- denomalized columns
, source_object_type_code
, task_type_id
, task_type_rule
, deleted_flag
, act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type
, act_bac_district_id
, inventory_item_id
, inv_organization_id
, customer_id
, planned_start_date
-- denomalized columns
)
values
( task_id
, task_audit_id
, greatest(backlog_date_from, g_global_start_date)
, greatest(backlog_date_to, g_global_start_date)
, backlog_status_code
, g_user_id
, sysdate
, g_user_id
, sysdate
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
-- denomalized columns
, source_object_type_code
, task_type_id
, task_type_rule
, deleted_flag
, act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type
, act_bac_district_id
, inventory_item_id
, inv_organization_id
, customer_id
, planned_start_date
-- denomalized columns
)
select
task_id
, task_audit_id
, trunc(audit_date) backlog_date_from
, lead(trunc(audit_date)-1,1,g_max_date)
over(partition by task_id order by audit_date, task_audit_id) backlog_date_to
, backlog_status_code
, backlog_rowid
, status_flag
-- denomalized columns
, source_object_type_code
, task_type_id
, task_type_rule
, deleted_flag
, act_bac_assignee_id
-- R12 resource type impact
, act_bac_assignee_type
, act_bac_district_id
, inventory_item_id
, inv_organization_id
, customer_id
, planned_start_date
-- denomalized columns
from
isc_fs_task_act_bac_stg
where
status_flag in ('O','C')
and last_row_for_day_flag = 'Y';
bis_collection_utilities_log( l_temp_rowcount || ' rows inserted', 2 );