[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select period_year, quarter_num, period_num, start_date, quarter_start_date, year_start_date
from gl_periods
where adjustment_period_flag='N'
and period_set_name=g_period_set_name
and period_type=g_period_type
and day between start_date and end_date;
SELECT count(*) into l_count
FROM OZF_TIME_DAY
WHERE report_date = trunc(l_day);
if l_count = 0 then -- new record, insert
insert into OZF_TIME_DAY
(report_date,
report_date_julian,
start_date,
end_date,
month_id,
ent_period_id,
ent_period_start_date,
ent_qtr_id,
ent_qtr_start_date,
ent_year_id,
ent_year_start_date,
week_id,
week_start_date,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
values(
trunc(l_day),
to_char(l_day,'j'),
l_day,
l_day,
to_number(to_char(l_day,'yyyyqmm')),
--l_period_year||l_quarter_num||lpad(l_period_num,2,'0'),
l_period_year||l_quarter_num||decode(length(l_period_num),1,'0'||l_period_num, l_period_num),
l_start_date,
l_period_year||l_quarter_num,
l_quarter_start_date,
l_period_year,
l_year_start_date,
l_year_num||lpad(l_p445_num,2,'0')||lpad(l_week_num,2,'0'),
nvl(trunc(l_day-g_week_offset,'iw')+g_week_offset,trunc(sysdate)),
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
);
else -- the day has been loaded, update those changed records only
update OZF_TIME_DAY
set
ent_period_id = --l_period_year||l_quarter_num||lpad(l_period_num,2,'0'),
l_period_year||l_quarter_num||decode(length(l_period_num),1,'0'||l_period_num, l_period_num),
ent_period_start_date = l_start_date,
ent_qtr_id = l_period_year||l_quarter_num,
ent_qtr_start_date = l_quarter_start_date,
ent_year_id = l_period_year,
ent_year_start_date = l_year_start_date,
week_id = l_year_num||lpad(l_p445_num,2,'0')||lpad(l_week_num,2,'0'),
week_start_date = nvl(trunc(l_day-g_week_offset,'iw')+g_week_offset,trunc(sysdate)),
last_update_date = sysdate,
last_updated_by = g_user_id,
last_update_login = g_login_id
where report_date = trunc (l_day)
and (ent_period_id <> --l_period_year||l_quarter_num||lpad(l_period_num,2,'0')
l_period_year||l_quarter_num||decode(length(l_period_num),1,'0'||l_period_num, l_period_num) or
ent_period_start_date <> l_start_date or
NVL(ent_qtr_start_date, to_date('01/01/1000', 'DD/MM/YYYY')) <>
NVL(l_quarter_start_date, to_date('01/01/1000', 'DD/MM/YYYY')) or
NVL(ent_year_start_date, to_date('01/01/1000', 'DD/MM/YYYY')) <>
NVL(l_year_start_date, to_date('01/01/1000', 'DD/MM/YYYY')));
OZF_TP_UTIL_PVT.put_line(to_char(l_day_row)||' records has been populated or updated to Day Level');
select period_year, quarter_num, period_num, start_date, quarter_start_date, year_start_date
from gl_periods
where adjustment_period_flag='N'
and period_set_name=g_period_set_name
and period_type=g_period_type
and day between start_date and end_date;
insert into OZF_TIME_DAY
(report_date,
report_date_julian,
start_date,
end_date,
month_id,
ent_period_id,
ent_period_start_date,
ent_qtr_id,
ent_qtr_start_date,
ent_year_id,
ent_year_start_date,
week_id,
week_start_date,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
values(
trunc(l_day),
to_char(l_day,'j'),
l_day,
l_day,
to_number(to_char(l_day,'yyyyqmm')),
l_period_year||l_quarter_num||decode(length(l_period_num),1,'0'||l_period_num, l_period_num),
--l_period_year||l_quarter_num||lpad(l_period_num,2,'0'),
l_start_date,
l_period_year||l_quarter_num,
l_quarter_start_date,
l_period_year,
l_year_start_date,
l_year_num||lpad(l_p445_num,2,'0')||lpad(l_week_num,2,'0'),
trunc(l_day-g_week_offset,'iw')+g_week_offset,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
);
delete from OZF_TIME_WEEK where start_date <= l_to_date and end_date >= l_from_date;
insert into OZF_TIME_WEEK
(week_id,
period445_id,
sequence,
name,
start_date,
end_date,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
values
(
l_year_num||lpad(l_period_num,2,'0')||lpad(l_week_num,2,'0'),
l_year_num||lpad(l_period_num,2,'0'),
l_week_num,
to_char(l_week_end,'dd-Mon-rr'),
l_week,
l_week_end,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
);
delete from OZF_TIME_ENT_PERIOD where start_date <= l_to_date and end_date >= l_from_date;
insert into OZF_TIME_ENT_PERIOD
(ent_period_id,
ent_qtr_id,
ent_year_id,
sequence,
name,
start_date,
end_date,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
select
to_number(period_year||quarter_num||decode(length(period_num),1,'0'||period_num, period_num)),
--to_number(period_year||quarter_num||lpad(period_num,2,'0')),
to_number(period_year||quarter_num),
to_number(period_year),
period_num,
period_name,
start_date,
end_date,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from gl_periods
where period_set_name = g_period_set_name
and period_type = g_period_type
and adjustment_period_flag='N'
and start_date <= l_to_date
and end_date >= l_from_date;
delete from OZF_TIME_ENT_QTR where start_date <= l_to_date and end_date >= l_from_date;
insert into OZF_TIME_ENT_QTR
(ent_qtr_id,
ent_year_id,
sequence,
name,
start_date,
end_date,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
select distinct gl.period_year||gl.quarter_num,
gl.period_year,
gl.quarter_num,
replace(fnd_message.get_string('OZF','OZF_TP_QUARTER_LABEL'),'&QUARTER_NUMBER',gl.quarter_num)||'-'||to_char(to_date(gl.period_year,'yyyy'),'RR'),
gl2.start_date,
gl2.end_date,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from gl_periods gl,
(select period_year||quarter_num ent_qtr_pk_key, min(start_date) start_date, max(end_date) end_date
from gl_periods
where period_set_name=g_period_set_name
and period_type=g_period_type
and adjustment_period_flag='N'
group by period_year||quarter_num) gl2
where gl.period_year||gl.quarter_num = gl2.ent_qtr_pk_key
and gl.period_set_name = g_period_set_name
and gl.period_type = g_period_type
and gl.adjustment_period_flag='N'
and gl.start_date <= l_to_date
and gl.end_date >= l_from_date;
select nvl(max(end_date), l_to_date)
into l_end_date
from ozf_time_ent_period;
delete from OZF_TIME_ENT_YEAR where ent_year_id in
(select period_year
from gl_periods
where period_set_name = g_period_set_name
and period_type = g_period_type
and adjustment_period_flag='N'
and start_date <= l_to_date
and end_date >= l_from_date);
insert into OZF_TIME_ENT_YEAR
(ent_year_id,
period_set_name,
period_type,
sequence,
name,
start_date,
end_date,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
select distinct gl.period_year ent_year_pk_key,
gl.period_set_name period_set_name,
gl.period_type period_type,
gl.period_year,
gl.period_year name,
gl2.start_date start_date,
gl2.end_date end_date,
sysdate creation_date,
sysdate last_update_date,
g_user_id last_updated_by,
g_user_id created_by,
g_login_id last_update_login
from gl_periods gl,
(select period_year period_year, min(start_date) start_date, max(end_date) end_date
from gl_periods
where period_set_name=g_period_set_name
and period_type=g_period_type
and adjustment_period_flag='N'
and end_date <= l_end_date
group by period_year) gl2
where gl.period_year=gl2.period_year
and gl.period_set_name = g_period_set_name
and gl.period_type = g_period_type
and gl.adjustment_period_flag='N'
and gl.start_date <= l_to_date
and gl.end_date >= l_from_date;
select report_date, ent_period_start_date, ent_qtr_start_date,
ent_year_start_date, week_start_date
from OZF_TIME_DAY;
insert into OZF_TIME_RPT_STRUCT
(calendar_id,
calendar_type,
report_date,
time_id,
period_type_id,
record_type_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
values
(-1,
'C',
c1_rec.report_date,
to_char(c1_rec.report_date,'j'),
1,
1,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id);
insert into OZF_TIME_RPT_STRUCT
(calendar_id,
calendar_type,
report_date,
time_id,
period_type_id,
record_type_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
values
(-1,
'C',
c1_rec.report_date,
to_char(l_day,'j'),
1,
2,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id);
select nvl(min(start_date),l_week_start_date) into l_ptd_to_date from OZF_TIME_WEEK
where start_date >= l_period_start_date
and start_date < l_week_start_date;
insert into OZF_TIME_RPT_STRUCT
(calendar_id,
calendar_type,
report_date,
time_id,
period_type_id,
record_type_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
values
(-1,
'C',
c1_rec.report_date,
to_char(l_day,'j'),
1,
4,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id);
insert into OZF_TIME_RPT_STRUCT
(calendar_id,
calendar_type,
report_date,
time_id,
period_type_id,
record_type_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
values
(-1,
'C',
c1_rec.report_date,
to_char(l_day,'j'),
1,
8,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id);
insert into OZF_TIME_RPT_STRUCT
(calendar_id,
calendar_type,
report_date,
time_id,
period_type_id,
record_type_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
select
-1,
'E',
c1_rec.report_date,
week_id,
16,
16,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from OZF_TIME_WEEK
where start_date >= c1_rec.ent_period_start_date
and end_date < c1_rec.week_start_date;
insert into OZF_TIME_RPT_STRUCT
(calendar_id,
calendar_type,
report_date,
time_id,
period_type_id,
record_type_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
select
-1,
'E',
c1_rec.report_date,
ent_period_id,
32,
32,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from OZF_TIME_ENT_PERIOD
where start_date >= c1_rec.ent_qtr_start_date
and start_date <= c1_rec.ent_period_start_date
and end_date < c1_rec.report_date
union all
select
-1,
'E',
c1_rec.report_date,
ent_period_id,
32,
256,
--case when end_date >= c1_rec.report_date then 256 else 32 end,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from OZF_TIME_ENT_PERIOD
where start_date >= c1_rec.ent_qtr_start_date
and start_date <= c1_rec.ent_period_start_date
and end_date >= c1_rec.report_date;
insert into OZF_TIME_RPT_STRUCT
(calendar_id,
calendar_type,
report_date,
time_id,
period_type_id,
record_type_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
select
-1,
'E',
c1_rec.report_date,
ent_qtr_id,
64,
64,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from OZF_TIME_ENT_QTR
where start_date >= c1_rec.ent_year_start_date
and start_date <= c1_rec.ent_qtr_start_date
and end_date < c1_rec.report_date
union all
select
-1,
'E',
c1_rec.report_date,
ent_qtr_id,
64,
512,
--case when end_date >= c1_rec.report_date then 512 else 64 end,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from OZF_TIME_ENT_QTR
where start_date >= c1_rec.ent_year_start_date
and start_date <= c1_rec.ent_qtr_start_date
and end_date >= c1_rec.report_date;
insert into OZF_TIME_RPT_STRUCT
(calendar_id,
calendar_type,
report_date,
time_id,
period_type_id,
record_type_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
select
-1,
'E',
c1_rec.report_date,
ent_year_id,
128,
128,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from OZF_TIME_ENT_YEAR
where c1_rec.report_date between start_date and end_date;
insert into OZF_TIME_RPT_STRUCT
(calendar_id,
calendar_type,
report_date,
time_id,
period_type_id,
record_type_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
select
-1,
'E',
c1_rec.report_date,
ent_year_id,
128,
1024,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from OZF_TIME_ENT_YEAR
where end_date >= g_global_start_date -- should we use start_date?
and end_date < c1_rec.report_date;
select greatest(NVL(max(end_date)+1,ozf_common_parameters_pvt.get_global_start_date),
ozf_common_parameters_pvt.get_global_start_date)
into l_return_date
from ozf_time_day;
select least(nvl(min(start_date),ozf_common_parameters_pvt.get_global_start_date) ,
ozf_common_parameters_pvt.get_global_start_date)
into l_return_date
from ozf_time_day;
select max(end_date)
into l_return_date
from gl_periods
where adjustment_period_flag = 'N'
and period_set_name = l_period_set_name
and period_type = l_period_type;