[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select max(aging_date)
into l_max_aging_date
from isc_fs_task_bac_dates_c;
update isc_fs_task_bac_dates_c
set aging_date = l_collect_to_date
, last_update_date = sysdate
, last_updated_by = g_user_id
, 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 aging_date = l_max_aging_date;
bis_collection_utilities_log( 'Previous current as at date row updated in task current backlog age dates table', 2 );
insert
into isc_fs_task_bac_dates_c
( aging_date
, record_type_id
, xtd_end_date_flag
, week_start_date
, ent_period_start_date
, ent_qtr_start_date
, ent_year_start_date
, day_start_date
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
select
aging_date
, sum( power(2,id) ) record_type_id
, max(xtd_end_date_flag)
, max(week_start_date)
, max(ent_period_start_date)
, max(ent_qtr_start_date)
, max(ent_yr_start_date)
, max(day_start_date)
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
from
( select -- WTD
id
, aging_date
, decode(trunc(aging_date),end_date,'Y','N') xtd_end_date_flag
, start_date week_start_date
, to_date(null) ent_period_start_date
, to_date(null) ent_qtr_start_date
, to_date(null) ent_yr_start_date
, to_date(null) day_start_date
from
( select m.id
, least(fii.end_date+86399/86400, m.the_date) aging_date
, fii.start_date
, fii.end_date
, rank() over (partition by m.id order by fii.start_date desc ) rnk
from fii_time_week fii
, (select
id
, case
when id = 9 then FII_TIME_API.sd_lyswk(l_collect_to_date_trunc)+86399/86400
when id = 5 then FII_TIME_API.sd_pwk(l_collect_to_date_trunc)+86399/86400
else l_collect_to_date
end the_date
from oki_dbi_multiplexer_b
where id in (1,5,9)
) m
where fii.start_date < m.the_date
)
where rnk <= 13
--
union all
--
select -- MTD
id
, aging_date
, decode(trunc(aging_date),end_date,'Y','N')
, null
, start_date
, null
, null
, null
from
( select m.id
, least(fii.end_date+86399/86400, m.the_date) aging_date
, fii.start_date
, fii.end_date
, rank() over (partition by m.id order by fii.start_date desc ) rnk
from fii_time_ent_period fii
, (select
id
, case
when id = 10 then FII_TIME_API.ent_sd_lysper_end(l_collect_to_date_trunc)+86399/86400
when id = 6 then FII_TIME_API.ent_sd_pper_end(l_collect_to_date_trunc)+86399/86400
else l_collect_to_date
end the_date
from oki_dbi_multiplexer_b
where id in (2,6,10)
) m
where fii.start_date < m.the_date
)
where rnk <= 12
--
union all
--
select -- QTD
id
, aging_date
, decode(trunc(aging_date),end_date,'Y','N')
, null
, null
, start_date
, null
, null
from
( select m.id
, least(fii.end_date+86399/86400, m.the_date) aging_date
, fii.start_date
, fii.end_date
, rank() over (partition by m.id order by fii.start_date desc ) rnk
from fii_time_ent_qtr fii
, (select
id
, case
when id = 11 then FII_TIME_API.ent_sd_lysqtr_end(l_collect_to_date_trunc)+86399/86400
when id = 7 then FII_TIME_API.ent_sd_pqtr_end (l_collect_to_date_trunc)+86399/86400
else l_collect_to_date
end the_date
from oki_dbi_multiplexer_b
where id in (3,7,11)
) m
where fii.start_date < m.the_date
)
where (id in (3,7) and rnk <=8) or (id = 11 and rnk <= 4)
--
union all
--
select -- YTD
id
, aging_date
, decode(trunc(aging_date),end_date,'Y','N')
, null
, null
, null
, start_date
, null
from
( select m.id
, least(fii.end_date+86399/86400, m.the_date) aging_date
, fii.start_date
, fii.end_date
, rank() over (partition by m.id order by fii.start_date desc ) rnk
from fii_time_ent_year fii
, (select
id
, case
when id = 12 then FII_TIME_API.ent_sd_lyr_end(l_collect_to_date_trunc)+86399/86400
when id = 8 then FII_TIME_API.ent_sd_lyr_end(l_collect_to_date_trunc)+86399/86400
else l_collect_to_date
end the_date
from oki_dbi_multiplexer_b
where id in (4,8,12)
) m
where fii.start_date < m.the_date
)
where rnk <= 4
union all
select -- DAY
id
, aging_date
, 'N'
, null
, null
, null
, null
, start_date
from
( select m.id
, least(fii.report_date+86399/86400, m.the_date) aging_date
, fii.report_date start_date
, rank() over (partition by m.id order by fii.report_date desc ) rnk
from fii_time_day fii
, (select
id
, case
when id = 15 then FII_TIME_API.ent_sd_lyr_end(l_collect_to_date_trunc)+86399/86400
when id = 14 then (l_collect_to_date_trunc - 1)+86399/86400
else l_collect_to_date
end the_date
from oki_dbi_multiplexer_b
where id in (13,14,15)
) m
where fii.report_date < m.the_date
)
where rnk <= 7
)
group by aging_date;
bis_collection_utilities_log( x_rowcount || ' rows inserted into task current backlog age dates table', 2 );
insert /*+ append f */
into isc_fs_task_bac_dates_f f
( report_date
, aging_date
, xtd_end_date_flag
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
select
trunc(aging_date)
, aging_date
, xtd_end_date_flag
, sysdate
, g_user_id
, sysdate
, g_user_id
, g_login_id
, g_program_id
, g_program_login_id
, g_program_application_id
, g_request_id
from
isc_fs_task_bac_dates_c;
bis_collection_utilities_log( l_temp_rowcount || ' rows inserted into task backlog age dates base summary table', 1 );
delete from isc_fs_task_bac_dates_f
where report_date = (select max(report_date) from isc_fs_task_bac_dates_f)
and aging_date <> l_collect_to_date;
bis_collection_utilities_log( 'Previous current as at date row deleted from task backlog age dates base summary table', 1 );
delete from isc_fs_task_bac_dates_f
where report_date not in (select trunc(aging_date) from isc_fs_task_bac_dates_c);
bis_collection_utilities_log( l_temp_rowcount || ' rows deleted from task backlog age dates base summary table', 1 );
insert
into isc_fs_task_bac_dates_f
( report_date
, aging_date
, xtd_end_date_flag
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, program_id
, program_login_id
, program_application_id
, request_id
)
select
trunc(aging_date) report_date
, aging_date
, xtd_end_date_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
from
isc_fs_task_bac_dates_c c
where
trunc(aging_date) not in (select report_date from isc_fs_task_bac_dates_f);
bis_collection_utilities_log( l_temp_rowcount || ' rows inserted into task backlog age dates base summary table', 1 );