[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION period_updated(p_from_date in date, p_to_date in date) return varchar2 is
l_updated varchar2(1);
insert into FII_TIME_GL_PERIODS
(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),
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 <= p_to_date
and end_date >= p_from_date;
select 'Y'
into l_updated
from
(select ent_period_id,
ent_qtr_id,
ent_year_id,
--sequence,
name,
start_date,
end_date
from fii_time_ent_period
where end_date < g_unassigned_day
minus
select ent_period_id,
ent_qtr_id,
ent_year_id,
--sequence,
name,
start_date,
end_date
from FII_TIME_GL_PERIODS)
where rownum = 1;
l_updated := 'N';
return l_updated;
end period_updated;
select period_year, quarter_num, period_num, start_date, end_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 max(end_date) into l_quarter_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 period_year=l_period_year
and quarter_num=l_quarter_num;
select max(end_date) into l_year_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 period_year=l_period_year;
select 1 into l_count
from fii_time_day
where report_date = trunc(l_day)
and rownum = 1;
if l_count = 0 then -- new record, insert
insert into fii_time_day
(report_date,
report_date_julian,
start_date,
end_date,
month_id,
ent_period_id,
ent_period_start_date,
ent_period_end_date,
ent_qtr_id,
ent_qtr_start_date,
ent_qtr_end_date,
ent_year_id,
ent_year_start_date,
ent_year_end_date,
week_id,
week_start_date,
week_end_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),
-- lpad(l_period_num,2,'0'), bug 3370185
l_start_date,
l_end_date,
l_period_year||l_quarter_num,
l_quarter_start_date,
l_quarter_end_date,
l_period_year,
l_year_start_date,
l_year_end_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)),
nvl(trunc(l_day-g_week_offset,'iw')+g_week_offset,trunc(sysdate))+6, --week end date,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
);
else -- the day has been loaded, update those changed records only
update fii_time_day
set
ent_period_id = l_period_year||l_quarter_num||decode(length(l_period_num),1,'0'||l_period_num, l_period_num),
-- lpad(l_period_num,2,'0'), bug 3370185
ent_period_start_date = l_start_date,
ent_period_end_date = l_end_date,
ent_qtr_id = l_period_year||l_quarter_num,
ent_qtr_start_date = l_quarter_start_date,
ent_qtr_end_date = l_quarter_end_date,
ent_year_id = l_period_year,
ent_year_start_date = l_year_start_date,
ent_year_end_date = l_year_end_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)),
week_end_date = nvl(trunc(l_day-g_week_offset,'iw')+g_week_offset,trunc(sysdate))+6,
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||decode(length(l_period_num),1,'0'||l_period_num, l_period_num)
-- lpad(l_period_num,2,'0') bug 3370185
or
ent_period_start_date <> l_start_date or
ent_period_end_date <> l_end_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_qtr_end_date, to_date('01/01/1000', 'DD/MM/YYYY')) <>
NVL(l_quarter_end_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')) or
NVL(ent_year_end_date, to_date('01/01/1000', 'DD/MM/YYYY')) <>
NVL(l_year_end_date, to_date('01/01/1000', 'DD/MM/YYYY')));
fii_util.put_line('LOAD_DAY_INC : '||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 fii_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),
-- lpad(l_period_num,2,'0'), bug 3370185
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
);
select min(start_date) into l_min_date from fii_time_month;
delete from FII_TIME_MONTH
where start_date <= l_to_date and end_date >= l_from_date
or end_date >= g_unassigned_day;
insert into fii_time_month
(month_id,
quarter_id,
name,
start_date,
end_date,
prior_year_month_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
values
(
to_number(to_char(l_month,'yyyyqmm')),
to_number(to_char(l_month,'yyyyq')),
decode(l_month_end, g_unassigned_day, null,
to_char(l_month,'Mon YYYY')
),
l_month,
l_month_end,
decode(l_month_end, g_unassigned_day, null,
decode(to_char (l_min_date, 'YYYY'), to_char (l_month, 'YYYY'), NULL, to_number(to_char(add_months(l_month, -12),'yyyyqmm')))
),
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
);
select min(start_date) into l_min_date from fii_time_qtr;
delete from FII_TIME_QTR
where start_date <= l_to_date and end_date >= l_from_date
or end_date >= g_unassigned_day;
insert into fii_time_qtr
(quarter_id,
year_id,
name,
start_date,
end_date,
prior_year_quarter_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
values(
to_number(to_char(l_qtr,'yyyyq')),
to_number(to_char(l_qtr,'yyyy')),
decode(l_qtr_end, g_unassigned_day, null,
to_char(l_qtr,'q,yyyy')
),
l_qtr,
l_qtr_end,
decode(l_qtr_end, g_unassigned_day, null,
decode(to_char (l_min_date, 'YYYY'), to_char (l_qtr, 'YYYY'), NULL, to_number(to_char(add_months(l_qtr, -12),'yyyyq')))
),
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
);
select min(start_date) into l_min_date from fii_time_year;
delete from FII_TIME_YEAR
where start_date <= l_to_date and end_date >= l_from_date
or end_date >= g_unassigned_day;
insert into fii_time_year
(year_id,
name,
start_date,
end_date,
prior_year_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
values(
to_number(to_char(l_year,'yyyy')),
decode(l_year_end, g_unassigned_day, null,
to_number(to_char(l_year,'yyyy'))
),
l_year,
l_year_end,
decode(l_year_end, g_unassigned_day, null,
decode(to_char (l_min_date, 'YYYY'), to_char (l_year, 'YYYY'), NULL, to_number(to_char(add_months(l_year, -12),'yyyy')))
),
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
);
delete from FII_TIME_WEEK
where start_date <= l_to_date and end_date >= l_from_date
or end_date >= g_unassigned_day;
insert into fii_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,
decode(l_week_end, g_unassigned_day, null,
to_char(l_week_end,'dd-Mon-rr')
),
l_week,
l_week_end,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
);
update fii_time_day
set week_id = l_year_num||lpad(l_period_num,2,'0')||lpad(l_week_num,2,'0'),
week_start_date = l_week,
week_end_date = l_week_end,
last_update_date = sysdate,
last_updated_by = g_user_id,
last_update_login = g_login_id
where report_date = g_unassigned_day;
delete from FII_TIME_P445
where start_date <= l_to_date and end_date >= l_from_date
or end_date >= g_unassigned_day;
insert into fii_time_p445
(period445_id,
year445_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'),
l_year_num,
l_period_num,
decode(l_period_end, g_unassigned_day, null,
lpad(l_period_num,2,'0')||' '||l_year_num
),
l_period,
l_period_end,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
);
delete from FII_TIME_YEAR445
where start_date <= l_to_date and end_date >= l_from_date
or end_date >= g_unassigned_day;
insert into fii_time_year445
(year445_id,
name,
start_date,
end_date,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
values(
l_year_num,
decode(l_year_end, g_unassigned_day, null,
l_year_num
),
l_year,
l_year_end,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
);
select distinct ent_year_id
from fii_time_ent_period
where start_date <= p_to_date
and end_date >= p_from_date;
select ent_period_id
from fii_time_ent_period
where ent_year_id = p_year
order by start_date;
select period_year into l_period_year from gl_periods
where l_from_date between start_date and end_date
and period_set_name = g_period_set_name
and period_type = g_period_type
and adjustment_period_flag='N';
select period_year into l_period_year from gl_periods
where (select nvl(min(start_date), l_from_date) from fii_time_ent_period) between start_date and end_date
and period_set_name = g_period_set_name
and period_type = g_period_type
and adjustment_period_flag='N';
delete from FII_TIME_ENT_PERIOD
where start_date <= l_to_date and end_date >= l_from_date
or end_date >= g_unassigned_day;
insert into fii_time_ent_period
(ent_period_id,
ent_qtr_id,
ent_year_id,
sequence,
name,
start_date,
end_date,
prior_year_ent_period_id,
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)),
-- lpad(period_num,2,'0')), bug 3370185
to_number(period_year||quarter_num),
to_number(period_year),
period_num,
period_name,
start_date,
end_date,
decode(l_period_year, period_year, null, to_number((period_year-1)||quarter_num||decode(length(period_num),1,'0'||period_num, period_num))),
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;
update fii_time_ent_period
set sequence = l_period_seq
where ent_period_id = j.ent_period_id;
insert into fii_time_ent_period
(ent_period_id,
ent_qtr_id,
ent_year_id,
sequence,
name,
start_date,
end_date,
prior_year_ent_period_id,
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)),
-- lpad(period_num,2,'0')), bug 3370185
to_number(period_year||quarter_num),
to_number(period_year),
period_num,
decode(end_date, g_unassigned_day, null,
period_name
),
start_date,
end_date,
decode(end_date, g_unassigned_day, null,
decode(l_period_year, period_year, null, to_number((period_year-1)||quarter_num||decode(length(period_num),1,'0'||period_num, period_num)))
),
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from ( select
g_una_ent_period_year period_year,
g_una_ent_quarter_num quarter_num,
g_una_ent_period_num period_num,
null period_name,
g_unassigned_day start_date,
g_unassigned_day end_date
from dual );
update fii_time_day
set ent_period_id = to_number(g_una_ent_period_year||g_una_ent_quarter_num||decode(length(g_una_ent_period_num),1,'0'||g_una_ent_period_num, g_una_ent_period_num)),
ent_period_start_date = g_unassigned_day,
ent_period_end_date = g_unassigned_day,
ent_qtr_id = to_number(g_una_ent_period_year||g_una_ent_quarter_num),
ent_qtr_start_date = g_unassigned_day,
ent_qtr_end_date = g_unassigned_day,
ent_year_id = to_number(g_una_ent_period_year),
ent_year_start_date = g_unassigned_day,
ent_year_end_date = g_unassigned_day,
last_update_date = sysdate,
last_updated_by = g_user_id,
last_update_login = g_login_id
where report_date = g_unassigned_day;
select distinct ent_year_id
from fii_time_ent_period
where start_date <= p_to_date
and end_date >= p_from_date;
select distinct sequence, start_date, ent_qtr_id
from fii_time_ent_qtr
where ent_year_id = p_year
order by start_date;
select period_year into l_period_year from gl_periods
where l_from_date between start_date and end_date
and period_set_name = g_period_set_name
and period_type = g_period_type
and adjustment_period_flag='N';
select period_year into l_period_year from gl_periods
where (select nvl(min(start_date), l_from_date) from fii_time_ent_qtr) between start_date and end_date
and period_set_name = g_period_set_name
and period_type = g_period_type
and adjustment_period_flag='N';
delete from FII_TIME_ENT_QTR
where start_date <= l_to_date and end_date >= l_from_date
or end_date >= g_unassigned_day;
insert into fii_time_ent_qtr
(ent_qtr_id,
ent_year_id,
sequence,
name,
start_date,
end_date,
prior_year_ent_qtr_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
select distinct glp.period_year||glp.quarter_num,
glp.period_year,
glp.quarter_num,
replace(fnd_message.get_string('FII','FII_QUARTER_LABEL'),'&QUARTER_NUMBER',glp.quarter_num)||'-'||to_char(to_date(glp.period_year,'yyyy'),'RR'),
gl2.start_date,
gl2.end_date,
decode(l_period_year, period_year, NULL, (glp.period_year - 1)||glp.quarter_num),
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from gl_periods glp,
(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 glp.period_year||glp.quarter_num = gl2.ent_qtr_pk_key
and glp.period_set_name = g_period_set_name
and glp.period_type = g_period_type
and glp.adjustment_period_flag='N'
and glp.start_date <= l_to_date
and glp.end_date >= l_from_date;
update fii_time_ent_qtr
set sequence = l_qtr_seq
where ent_qtr_id = k.ent_qtr_id;
insert into fii_time_ent_qtr
(ent_qtr_id,
ent_year_id,
sequence,
name,
start_date,
end_date,
prior_year_ent_qtr_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
select distinct glp.period_year||glp.quarter_num,
glp.period_year,
glp.quarter_num,
decode(end_date, g_unassigned_day, null,
replace(fnd_message.get_string('FII','FII_QUARTER_LABEL'),'&QUARTER_NUMBER',glp.quarter_num)||'-'||to_char(to_date(glp.period_year,'yyyy'),'RR')
),
glp.start_date,
glp.end_date,
decode(end_date, g_unassigned_day, null,
decode(l_period_year, period_year, NULL, (glp.period_year - 1)||glp.quarter_num)
),
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from ( select
g_una_ent_period_year period_year,
g_una_ent_quarter_num quarter_num,
g_unassigned_day start_date,
g_unassigned_day end_date
from dual ) glp;
select period_year into l_period_year from gl_periods
where l_from_date between start_date and end_date
and period_set_name = g_period_set_name
and period_type = g_period_type
and adjustment_period_flag='N';
select period_year into l_period_year from gl_periods where (select nvl(min(start_date), l_from_date) from fii_time_ent_year)
between start_date and end_date
and period_set_name = g_period_set_name
and period_type = g_period_type
and adjustment_period_flag='N';
select nvl(max(end_date), l_to_date)
into l_end_date
from fii_time_ent_period
where end_date < g_unassigned_day;
delete from FII_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)
or end_date >= g_unassigned_day;
insert into fii_time_ent_year
(ent_year_id,
period_set_name,
period_type,
sequence,
name,
start_date,
end_date,
prior_ent_year_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
select distinct glp.period_year ent_year_pk_key,
glp.period_set_name period_set_name,
glp.period_type period_type,
glp.period_year,
glp.period_year name,
gl2.start_date start_date,
gl2.end_date end_date,
decode(l_period_year, glp.period_year, NULL, (glp.period_year - 1)),
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 glp,
(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 glp.period_year=gl2.period_year
and glp.period_set_name = g_period_set_name
and glp.period_type = g_period_type
and glp.adjustment_period_flag='N'
and glp.start_date <= l_to_date
and glp.end_date >= l_from_date;
insert into fii_time_ent_year
(ent_year_id,
period_set_name,
period_type,
sequence,
name,
start_date,
end_date,
prior_ent_year_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
select distinct glp.period_year ent_year_pk_key,
glp.period_set_name period_set_name,
glp.period_type period_type,
glp.period_year,
decode(end_date, g_unassigned_day, null,
glp.period_year
),
glp.start_date start_date,
glp.end_date end_date,
decode(end_date, g_unassigned_day, null,
decode(l_period_year, glp.period_year, NULL, (glp.period_year - 1))
),
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 ( select
g_una_ent_period_year period_year,
g_period_set_name period_set_name,
g_period_type period_type,
g_unassigned_day start_date,
g_unassigned_day end_date
from dual ) glp;
Insert into fii_time_cal_gt(calendar_id)
select calendar_id
from (
(select
to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num||decode(length(glp.period_num),1,'0'||glp.period_num, glp.period_num))
-- lpad(gl.period_num,2,'0')) bug 3370185
, to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num)
, to_number(lpad(cal_name.calendar_id,3,'0')) calendar_id
, glp.period_num
, glp.period_name
, glp.start_date
, glp.end_date
from
gl_periods glp
, fii_time_cal_name cal_name
where glp.adjustment_period_flag = 'N'
and glp.period_set_name = cal_name.period_set_name
and glp.period_type = cal_name.period_type
minus
select
cal_period_id
, cal_qtr_id
, calendar_id
, sequence
, name
, start_date
, end_date
from
fii_time_cal_period
)
union all
(select
cal_period_id
, cal_qtr_id
, calendar_id
, sequence
, name
, start_date
, end_date
from
fii_time_cal_period
minus
select
to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num||decode(length(glp.period_num),1,'0'||glp.period_num, glp.period_num))
-- lpad(gl.period_num,2,'0')) bug 3370185
, to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num)
, to_number(lpad(cal_name.calendar_id,3,'0')) calendar_id
, glp.period_num
, glp.period_name
, glp.start_date
, glp.end_date
from
gl_periods glp
, fii_time_cal_name cal_name
where glp.adjustment_period_flag = 'N'
and glp.period_set_name = cal_name.period_set_name
and glp.period_type = cal_name.period_type
)
);
select 1 into l_diff_rows
from fii_time_cal_gt
where rownum = 1;
insert into fii_time_cal_period
(cal_period_id,
cal_qtr_id,
calendar_id,
sequence,
name,
start_date,
end_date,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
select lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num||decode(length(glp.period_num),1,'0'||glp.period_num, glp.period_num),
-- lpad(gl.period_num,2,'0'), bug 3370185
lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num,
lpad(cal_name.calendar_id,3,'0'),
glp.period_num,
glp.period_name,
glp.start_date,
glp.end_date,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from gl_periods glp, fii_time_cal_name cal_name
where glp.adjustment_period_flag='N'
and glp.period_set_name=cal_name.period_set_name
and glp.period_type=cal_name.period_type;
select 1
into l_diff_rows
from (
(select
distinct to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num)
, to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year)
, to_number(lpad(cal_name.calendar_id,3,'0'))
, glp.quarter_num
, to_char(glp.quarter_num)||', '||to_char(glp.period_year)
, min(gl2.start_date)
, max(gl2.end_date)
from
gl_periods glp
, fii_time_cal_name cal_name
, (select period_set_name
, period_type
, period_year
, quarter_num
, min(start_date) start_date
, max(end_date) end_date
from
gl_periods
where adjustment_period_flag='N'
group by
period_set_name
, period_type
, period_year
, quarter_num
) gl2
where glp.adjustment_period_flag='N'
and glp.period_set_name=cal_name.period_set_name
and glp.period_type=cal_name.period_type
and glp.period_set_name=gl2.period_set_name
and glp.period_type=gl2.period_type
and glp.period_year=gl2.period_year
and glp.quarter_num=gl2.quarter_num
group by
to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num)
, to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year)
, to_number(lpad(cal_name.calendar_id,3,'0'))
, glp.quarter_num
, to_char(glp.quarter_num)||', '||to_char(glp.period_year)
minus
select
cal_qtr_id
, cal_year_id
, calendar_id
, sequence
, name
, start_date
, end_date
from
fii_time_cal_qtr
)
union all
(select
cal_qtr_id
, cal_year_id
, calendar_id
, sequence
, name
, start_date
, end_date
from
fii_time_cal_qtr
minus
select
distinct to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num)
, to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year)
, to_number(lpad(cal_name.calendar_id,3,'0'))
, glp.quarter_num
, to_char(glp.quarter_num)||', '||to_char(glp.period_year)
, min(gl2.start_date)
, max(gl2.end_date)
from
gl_periods glp
, fii_time_cal_name cal_name
, (select period_set_name
, period_type
, period_year
, quarter_num
, min(start_date) start_date
, max(end_date) end_date
from
gl_periods
where adjustment_period_flag = 'N'
group by
period_set_name
, period_type
, period_year
, quarter_num
) gl2
where glp.adjustment_period_flag = 'N'
and glp.period_set_name = cal_name.period_set_name
and glp.period_type = cal_name.period_type
and glp.period_set_name = gl2.period_set_name
and glp.period_type = gl2.period_type
and glp.period_year = gl2.period_year
and glp.quarter_num = gl2.quarter_num
group by
to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num)
, to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year)
, to_number(lpad(cal_name.calendar_id,3,'0'))
, glp.quarter_num
, to_char(glp.quarter_num)||', '||to_char(glp.period_year)
)
)
where rownum = 1;
insert into fii_time_cal_qtr
(cal_qtr_id,
cal_year_id,
calendar_id,
sequence,
name,
start_date,
end_date,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
select distinct lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num,
lpad(cal_name.calendar_id,3,'0')||glp.period_year,
lpad(cal_name.calendar_id,3,'0'),
glp.quarter_num,
to_char(glp.quarter_num)||', '||to_char(glp.period_year),
min(gl2.start_date),
max(gl2.end_date),
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from gl_periods glp, fii_time_cal_name cal_name,
(select period_set_name, period_type, period_year, quarter_num, min(start_date) start_date, max(end_date) end_date
from gl_periods
where adjustment_period_flag='N'
group by period_set_name, period_type, period_year, quarter_num) gl2
where glp.adjustment_period_flag='N'
and glp.period_set_name=cal_name.period_set_name
and glp.period_type=cal_name.period_type
and glp.period_set_name=gl2.period_set_name
and glp.period_type=gl2.period_type
and glp.period_year=gl2.period_year
and glp.quarter_num=gl2.quarter_num
group by lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num,
lpad(cal_name.calendar_id,3,'0')||glp.period_year,
lpad(cal_name.calendar_id,3,'0'),
glp.quarter_num,
to_char(glp.quarter_num)||', '||to_char(glp.period_year);
select 1
into l_diff_rows
from (
(select
distinct to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year)
, to_number(lpad(cal_name.calendar_id,3,'0'))
, glp.period_year
, to_char(glp.period_year)
, min(gl2.start_date)
, max(gl2.end_date)
from
gl_periods glp
, fii_time_cal_name cal_name
, (select period_set_name
, period_type
, period_year
, min(start_date) start_date
, max(end_date) end_date
from
gl_periods
where adjustment_period_flag='N'
group by
period_set_name
, period_type
, period_year
) gl2
where glp.adjustment_period_flag = 'N'
and glp.period_set_name = cal_name.period_set_name
and glp.period_type = cal_name.period_type
and glp.period_set_name = gl2.period_set_name
and glp.period_type = gl2.period_type
and glp.period_year = gl2.period_year
group by
cal_name.calendar_id
, glp.period_year
minus
select
cal_year_id
, calendar_id
, sequence
, name
, start_date
, end_date
from
fii_time_cal_year
)
union all
(select
cal_year_id
, calendar_id
, sequence
, name
, start_date
, end_date
from
fii_time_cal_year
minus
select
distinct to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year)
, to_number(lpad(cal_name.calendar_id,3,'0'))
, glp.period_year
, to_char(glp.period_year)
, min(gl2.start_date)
, max(gl2.end_date)
from
gl_periods glp
, fii_time_cal_name cal_name
, (select period_set_name
, period_type
, period_year
, min(start_date) start_date
, max(end_date) end_date
from
gl_periods
where adjustment_period_flag = 'N'
group by
period_set_name
, period_type
, period_year
) gl2
where glp.adjustment_period_flag = 'N'
and glp.period_set_name = cal_name.period_set_name
and glp.period_type = cal_name.period_type
and glp.period_set_name = gl2.period_set_name
and glp.period_type = gl2.period_type
and glp.period_year = gl2.period_year
group by
cal_name.calendar_id
, glp.period_year
)
)
where rownum = 1;
insert into fii_time_cal_year
(cal_year_id,
calendar_id,
sequence,
name,
start_date,
end_date,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
select distinct lpad(cal_name.calendar_id,3,'0')||glp.period_year,
lpad(cal_name.calendar_id,3,'0'),
glp.period_year,
glp.period_year,
min(gl2.start_date),
max(gl2.end_date),
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from gl_periods glp, fii_time_cal_name cal_name,
(select period_set_name, period_type, period_year, min(start_date) start_date, max(end_date) end_date
from gl_periods
where adjustment_period_flag='N'
group by period_set_name, period_type, period_year) gl2
where glp.adjustment_period_flag='N'
and glp.period_set_name=cal_name.period_set_name
and glp.period_type=cal_name.period_type
and glp.period_set_name=gl2.period_set_name
and glp.period_type=gl2.period_type
and glp.period_year=gl2.period_year
group by cal_name.calendar_id, glp.period_year;
l_period_updated varchar2(1) := 'N';
select max(report_date), min(report_date)
into l_max_date, l_min_date
from fii_time_day
where report_date <> g_unassigned_day;
l_period_updated := period_updated(l_min_date, l_max_date);
IF (l_period_updated = 'Y' and p_load_mode <> 'INIT') THEN
l_start_date := l_min_date;
END IF; --l_period_updated
select period_year into l_year
from gl_periods a
where a.period_set_name = g_period_set_name
and a.period_type = g_period_type
and a.adjustment_period_flag = 'N'
and l_start_date between a.start_date and a.end_date;
select min(a.start_date), max(end_date) into l_min_start_date, l_max_end_date
from gl_periods a
where a.period_set_name = g_period_set_name
and a.period_type = g_period_type
and a.adjustment_period_flag = 'N'
and a.period_year = l_year;
select min(a.start_date), max(end_date) into l_min_start_date, l_max_end_date
from gl_periods a
where a.period_set_name = g_period_set_name
and a.period_type = g_period_type
and a.adjustment_period_flag = 'N'
and a.period_year = l_year;
fii_util.put_line('LOAD : '||'l_period_updated='||l_period_updated);
if l_period_updated = 'Y' then
truncate_table('FII_TIME_ENT_PERIOD');
select max(end_date)
into l_max_gl_date
from gl_periods
where period_set_name = g_period_set_name
and period_type = g_period_type
and adjustment_period_flag='N';
select 1 into l_count
from fii_time_day
where ent_period_end_date is null
and report_date <> g_unassigned_day
and rownum = 1;
if l_period_updated = 'Y' or l_max_gl_date > l_max_date or l_count > 0 then
-- we populate data from the earliest date in FII_TIME_DAY or the from date parameter, see which one is the earliest
-- to the latest date in FII_TIME_DAY or the to date parameter, see which one is the latest. This is to make sure
-- we will insert new records as well as modify existing records if necessary. If new gl period has been added to
-- an existing quarter, the quarter end date and the year end date needs to be updated.
LOAD_DAY_INC(least(nvl(l_min_date,l_from_date),l_from_date), greatest(nvl(l_max_date,l_to_date),l_to_date));
if l_period_updated = 'Y' then
-- we populate data from the earliest date in FII_TIME_DAY or the from date parameter, see which one is the earliest
-- to the latest date in FII_TIME_DAY or the to date parameter, see which one is the latest. This is to make sure
-- we will insert new records as well as existing records that we have been truncated
LOAD_ENT_PERIOD(least(nvl(l_min_date,l_from_date),l_from_date), greatest(nvl(l_max_date,l_to_date),l_to_date));
select 1 into l_count
from FII_TIME_STRUCTURES
where bitand( record_type_id, 16384+32768+65536 ) <> 0
and rownum = 1;
select 1 into l_count
from fii_time_rolling_offsets
where rownum = 1;
SELECT inline_view.period_set_name, inline_view.period_type, MAX(gp.creation_date)
FROM ( SELECT DISTINCT glp.period_set_name , glp.period_type
FROM gl_periods glp
MINUS
SELECT DISTINCT cal.period_set_name, cal.period_type
FROM fii_time_cal_name cal
) inline_view,
gl_periods gp
WHERE inline_view.period_set_name = gp.period_set_name
and inline_view.period_type = gp.period_type
GROUP BY inline_view.period_set_name, inline_view.period_type
ORDER BY MAX(gp.creation_date);
select nvl(max(calendar_id),0)
into l_max_cal_name
from fii_time_cal_name;
insert into fii_time_cal_name
(calendar_id,
period_set_name,
period_type,
name,
creation_date,
last_update_date,
last_updated_by,
created_by,
last_update_login)
values(
l_max_cal_name+1,
new_cal_rec.period_set_name,
new_cal_rec.period_type,
new_cal_rec.period_set_name||' ('||new_cal_rec.period_type||')',
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id);
select report_date, ent_period_start_date, ent_qtr_start_date,
ent_year_start_date, week_start_date
from FII_TIME_DAY
where report_date <> g_unassigned_day;
insert into FII_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 FII_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 FII_TIME_WEEK
where start_date >= l_period_start_date
and start_date < l_week_start_date;
insert into FII_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 FII_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 FII_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 FII_TIME_WEEK
where start_date >= c1_rec.ent_period_start_date
and end_date < c1_rec.week_start_date
union all
select
-1,
'E',
c1_rec.report_date,
week_id,
16,
2048,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from FII_TIME_WEEK
where c1_rec.report_date between start_date and end_date;
insert into FII_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 FII_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,
sysdate,
sysdate,
g_user_id,
g_user_id,
g_login_id
from FII_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 FII_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 FII_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 FII_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 FII_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 FII_TIME_ENT_YEAR
where c1_rec.report_date between start_date and end_date;
insert into FII_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 FII_TIME_ENT_YEAR
where end_date >= g_global_start_date -- should we use start_date?
and end_date < c1_rec.report_date;
SELECT nvl(least(min(report_date),l_from_date),l_from_date),nvl(greatest(max(report_date),l_to_date),l_to_date)
INTO l_min_date,l_max_date
from FII_TIME_CAL_RPT_STRUCT;
SELECT distinct calendar_id
FROM fii_time_cal_gt) LOOP
--Reset the l_from_date and l_to_date to the parameters passed
l_from_date:=p_from_date;
SELECT 'T'
INTO l_full_extraction_flag
FROM FII_TIME_CAL_RPT_STRUCT
WHERE REPORT_DATE >= p_from_date
AND CALENDAR_ID = cur_Fiscal_Calendar.CALENDAR_ID
AND ROWNUM <= 1;
-- Delete all records for the calendar
-- and reset the from and to date for
-- extraction
DELETE FII_TIME_CAL_RPT_STRUCT
WHERE CALENDAR_ID = cur_Fiscal_Calendar.CALENDAR_ID;
SELECT calendar_id
, report_date
, cal_period_start_date period_start_date
, cal_qtr_start_date qtr_start_date
, cal_year_start_date year_start_date
FROM fii_time_cal_day_mv
WHERE calendar_id = cur_Fiscal_Calendar.calendar_id
AND report_date BETWEEN l_from_date AND l_to_date) LOOP
INSERT INTO FII_TIME_CAL_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
(cur_Fiscal_Days.calendar_id
, 'C'
, cur_Fiscal_Days.report_date
, TO_CHAR(cur_Fiscal_Days.report_date,'j')
, 1
, 1
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id);
SELECT week_start_date
INTO l_Week_Start_Date
FROM fii_time_day
WHERE report_date = cur_Fiscal_Days.report_date;
SELECT NVL(MIN(start_date),l_Week_Start_Date)
INTO l_Earliest_Week
FROM fii_time_week
WHERE start_date >= cur_Fiscal_Days.period_start_date
AND start_date < l_Week_Start_Date;
INSERT INTO FII_TIME_CAL_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
(cur_Fiscal_Days.calendar_id
, 'C'
, cur_Fiscal_Days.report_date
, TO_CHAR(l_Day,'j')
, 1
, 2
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id);
INSERT INTO FII_TIME_CAL_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
(cur_Fiscal_Days.calendar_id
, 'C'
, cur_Fiscal_Days.report_date
, TO_CHAR(l_Day,'j')
, 1
, 4
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id);
INSERT INTO FII_TIME_CAL_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
cur_Fiscal_Days.calendar_id
, 'E'
, cur_Fiscal_Days.report_date
, week_id
, 16
, 16
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM FII_TIME_WEEK
WHERE start_date >= cur_Fiscal_Days.period_start_date
AND end_date < l_Week_Start_Date;
INSERT INTO FII_TIME_CAL_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
cur_Fiscal_Days.calendar_id
, 'C'
, cur_Fiscal_Days.report_date
, report_date_julian
, 1
, 4
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM FII_TIME_CAL_DAY_MV
WHERE calendar_id = cur_Fiscal_Days.calendar_id
AND report_date BETWEEN cur_Fiscal_Days.period_start_date AND l_Day;
INSERT INTO FII_TIME_CAL_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
cur_Fiscal_Days.calendar_id
, 'G'
, cur_Fiscal_Days.report_date
, cal_period_id
, 32
, 32
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM FII_TIME_CAL_PERIOD
WHERE start_date >= cur_Fiscal_Days.qtr_start_date
AND start_date <= cur_Fiscal_Days.period_start_date
AND end_date < cur_Fiscal_Days.report_date
AND calendar_id = cur_Fiscal_Days.calendar_id
UNION ALL
SELECT
cur_Fiscal_Days.calendar_id
, 'G'
, cur_Fiscal_Days.report_date
, cal_period_id
, 32
, 256
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM FII_TIME_CAL_PERIOD
WHERE start_date >= cur_Fiscal_Days.qtr_start_date
AND start_date <= cur_Fiscal_Days.period_start_date
AND end_date >= cur_Fiscal_Days.report_date
AND calendar_id = cur_Fiscal_Days.calendar_id;
INSERT INTO FII_TIME_CAL_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
cur_Fiscal_Days.calendar_id
, 'G'
, cur_Fiscal_Days.report_date
, cal_qtr_id
, 64
, 64
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM FII_TIME_CAL_QTR
WHERE start_date >= cur_Fiscal_Days.year_start_date
AND start_date <= cur_Fiscal_Days.qtr_start_date
AND end_date < cur_Fiscal_Days.report_date
AND calendar_id = cur_Fiscal_Days.calendar_id
UNION ALL
SELECT
cur_Fiscal_Days.calendar_id
, 'G'
, cur_Fiscal_Days.report_date
, cal_qtr_id
, 64
, 512
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM FII_TIME_CAL_QTR
WHERE start_date >= cur_Fiscal_Days.year_start_date
AND start_date <= cur_Fiscal_Days.qtr_start_date
AND end_date >= cur_Fiscal_Days.report_date
AND calendar_id = cur_Fiscal_Days.calendar_id;
INSERT INTO FII_TIME_CAL_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
cur_Fiscal_Days.calendar_id
, 'G'
, cur_Fiscal_Days.report_date
, cal_year_id
, 128
, 128
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM FII_TIME_CAL_YEAR
WHERE calendar_id = cur_Fiscal_Days.calendar_id
AND cur_Fiscal_Days.report_date BETWEEN start_date AND end_date
UNION ALL
SELECT
cur_Fiscal_Days.calendar_id
, 'G'
, cur_Fiscal_Days.report_date
, cal_year_id
, 128
, 1024
, SYSDATE
, SYSDATE
, g_user_id
, g_user_id
, g_login_id
FROM FII_TIME_CAL_YEAR
WHERE calendar_id = cur_Fiscal_Days.calendar_id
AND end_date < cur_Fiscal_Days.report_date
AND end_date >= g_global_start_date;
select least(nvl(min(start_date),bis_common_parameters.get_global_start_date) , bis_common_parameters.get_global_start_date) into l_return_date
from fii_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;