[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_rows_inserted Number:=0;
rows_inserted number:=0;
FII_POPULATE_TIME.Push(errbuf,retcode,rows_inserted,c_from_date,c_to_date);
g_rows_inserted:=g_rows_inserted+rows_inserted;
EDW_COLLECTION_UTIL.wrapup(TRUE, g_rows_inserted, null,
FII_TIME_M_C.g_push_date_range1, FII_TIME_M_C.g_push_date_range2);
Select per.period_set_name,
per.period_type,
min(start_date),
max(end_date)
From gl_periods per,
gl_sets_of_books book
where per.ADJUSTMENT_PERIOD_FLAG = 'N'
and per.period_set_name = book.period_set_name
and per.period_type = book.accounted_period_type
and per.end_date >= NVL(p_from_date, per.end_date)
and per.start_date <= NVL(p_to_date, per.start_date)
Group By per.period_set_name,
per.period_type;
Select per.period_set_name,
per.period_type,
min(start_date),
max(end_date)
From gl_periods per,
gl_sets_of_books book
where
per.period_set_name = book.period_set_name
and per.period_type = book.accounted_period_type
Group By per.period_set_name,
per.period_type;
select distinct sob.period_set_name,
paprd.period_name,
imp.pa_period_type,
map.accounting_date,
paprd.start_date,
paprd.end_date,
paprd.gl_period_name,
(glprd.period_year * 10000) + glprd.period_num
from gl_date_period_map map,
pa_implementations_all imp,
gl_sets_of_books sob,
pa_periods_all paprd,
gl_periods glprd
where imp.set_of_books_id = sob.set_of_books_id
and paprd.org_id = imp.org_id
and map.period_type = imp.pa_period_type
and map.period_name = paprd.period_name
and map.period_set_name = sob.period_set_name
and map.accounting_date >= NVL(p_from_date, map.accounting_date)
and map.accounting_date <= NVL(p_to_date, map.accounting_date)
and map.period_name <> 'NOT ASSIGNED'
and map.period_set_name = glprd.period_set_name
and map.period_name = glprd.period_name;
select distinct sob.period_set_name,
paprd.period_name,
paprd.start_date,
paprd.end_date,
paprd.gl_period_name,
imp.pa_period_type,
(glprd.period_year * 10000) + glprd.period_num
from pa_implementations_all imp,
gl_sets_of_books sob,
pa_periods_all paprd,
gl_periods glprd
where imp.set_of_books_id = sob.set_of_books_id
and paprd.org_id = imp.org_id
and paprd.end_date >= NVL(p_from_date, paprd.end_date)
and paprd.start_date <= NVL(p_to_date, paprd.start_date)
and paprd.period_name <> 'NOT ASSIGNED'
and paprd.period_name = glprd.period_name
and sob.period_set_name = glprd.period_set_name;
select distinct
per.period_set_name,
per.period_type,
per.period_name,
per.period_year,
per.quarter_num,
per.start_date,
per.end_date,
(per.period_year * 10000) + per.period_num
from gl_periods per,
gl_sets_of_books book
where per.adjustment_period_flag = 'N'
and per.period_set_name = book.period_set_name
and per.period_type = book.accounted_period_type;
Select per.period_set_name,
per.period_type,
per.period_year,
per.quarter_num,
min(per.start_date),
max(per.end_date)
from gl_periods per,
gl_sets_of_books book
where per.period_set_name = book.period_set_name
and per.period_type = book.accounted_period_type
group by per.period_set_name,
per.period_type,
per.period_year,
per.quarter_num;
Select per.period_set_name,
period_type,
period_year,
min(start_date),
max(end_date)
from gl_periods per,
gl_sets_of_books book
where per.period_set_name = book.period_set_name
and per.period_type = book.accounted_period_type
group by per.period_set_name,
per.period_type,
per.period_year;
select instance_code
into l_master_instance
from edw_local_system_parameters;
delete edw_time_cal_period_lstg
where instance = l_instance;
delete edw_time_pa_period_lstg
where instance = l_instance;
delete edw_time_cal_qtr_lstg
where instance = l_instance;
delete edw_time_cal_year_lstg
where instance = l_instance;
delete edw_time_cal_name_lstg
where instance = l_instance;
delete edw_time_cal_day_lstg
where instance = l_instance;
delete edw_time_ep_cal_period_lstg
where instance = l_instance;
delete edw_time_ep_cal_qtr_lstg
where instance = l_instance;
delete edw_time_ep_cal_year_lstg
where instance = l_instance;
delete edw_time_ep_cal_name_lstg
where instance = l_instance;
Select period_name,
start_date,
end_date,
(period_year * 10000) + period_num
into l_period_name,
l_start_date,
l_end_date,
l_effective_period_num
from gl_periods
where period_set_name = l_period_set_name
and period_type = l_period_type
and start_date <= l_gl_date
and end_date >= l_gl_date
and adjustment_period_flag = 'N';
Insert into EDW_TIME_CAL_DAY_LSTG
(
CAL_DAY_PK,
FA_PERIOD_FK,
PA_PERIOD_FK,
DAY_FK,
CAL_PERIOD_FK,
HOLIDAY_FLAG,
INSTANCE,
NAME,
WORK_DAY_FLAG,
CALENDAR_DATE,
END_DATE,
TIMESPAN,
SEQ_NUMBER,
COLLECTION_STATUS,
EP_CAL_PERIOD_FK,
EFFECTIVE_PERIOD_NUM,
PERIOD_SET_NAME,
PERIOD_TYPE
)
values(
to_char(l_gl_date,'dd-mm-yyyy') ||'-'||
l_period_set_name ||'-'||
l_period_type ||'-'||
l_instance|| '-CD', --cal_day_pk
'NA_EDW', --fa_period_fk
l_pa_period_fk, --pa_period_fk
to_char(l_gl_date,'dd-mm-yyyy'), --day_fk
l_gl_period_fk, --cal_period_fk
null, --holiday_flag
l_instance, --instance
to_char(l_gl_date,'fmdd Month yyyy')||' ('||l_period_set_name||')', --name
null,
l_gl_date,
l_gl_date,
1,
null,
'READY',
'oracle_source',
l_effective_period_num,
l_period_set_name,
l_period_type
);
Insert into EDW_TIME_CAL_DAY_LSTG
(
CAL_DAY_PK,
FA_PERIOD_FK,
PA_PERIOD_FK,
DAY_FK,
CAL_PERIOD_FK,
HOLIDAY_FLAG,
INSTANCE,
NAME,
WORK_DAY_FLAG,
CALENDAR_DATE,
END_DATE,
TIMESPAN,
SEQ_NUMBER,
COLLECTION_STATUS,
EP_CAL_PERIOD_FK,
EFFECTIVE_PERIOD_NUM,
PERIOD_SET_NAME,
PERIOD_TYPE
)
VALUES(
to_char(e_start_date,'dd-mm-yyyy') ||'-'||
l_period_set_name ||'-'||
l_period_type ||'-'||
l_instance|| '-CD', --cal_day_pk
'NA_EDW', --fa_period_fk
'NA_EDW', --pa_period_fk
to_char(e_start_date,'dd-mm-yyyy'), --day_fk
'NA_EDW', --cal_period_fk
null, --holiday_flag
l_instance, --instance
to_char(e_start_date,'fmdd Month yyyy')||' ('||l_period_set_name||')', --name
null,
e_start_date,
e_start_date,
1,
null,
'READY',
'oracle_source',
-1,
l_period_set_name,
l_period_type
);
Insert into EDW_TIME_CAL_DAY_LSTG
(CAL_DAY_PK,
FA_PERIOD_FK,
PA_PERIOD_FK,
DAY_FK,
CAL_PERIOD_FK,
HOLIDAY_FLAG,
INSTANCE,
NAME,
WORK_DAY_FLAG,
CALENDAR_DATE,
END_DATE,
TIMESPAN,
SEQ_NUMBER,
COLLECTION_STATUS,
EP_CAL_PERIOD_FK,
EFFECTIVE_PERIOD_NUM,
PERIOD_SET_NAME,
PERIOD_TYPE
)
values(to_char(l_gl_date,'dd-mm-yyyy')||'-'||
l_period_set_name||'-'||
l_period_type||'-'||
l_instance||'-PD', --cal_day_pk
'NA_EDW', --fa_period_fk
l_period_set_name||'-'||
l_pa_period_name||'-'||
l_instance||'-PA', --pa_period_fk
'NA_EDW', --day_fk
l_period_set_name||'-'||
l_period_name||'-'||l_instance, --cal_period_fk
null, --holiday_flag
l_instance, --instance
to_char(l_gl_date, 'fmdd Month yyyy')||
' ('||l_period_set_name||')', --name
null, --work_day_flag
l_gl_date, --calendar_date
l_gl_date, --end_date
1, --timespan
null, --seq_number
'READY', --collection_status
'oracle_source', --ep_cal_period_fk
l_effective_period_num,
l_period_set_name,
l_period_type
);
Insert into EDW_TIME_CAL_DAY_LSTG
(CAL_DAY_PK,
FA_PERIOD_FK,
PA_PERIOD_FK,
DAY_FK,
CAL_PERIOD_FK,
EP_CAL_PERIOD_FK,
HOLIDAY_FLAG,
INSTANCE,
NAME,
WORK_DAY_FLAG,
CALENDAR_DATE,
END_DATE,
TIMESPAN,
SEQ_NUMBER,
COLLECTION_STATUS,
EFFECTIVE_PERIOD_NUM,
PERIOD_SET_NAME,
PERIOD_TYPE
)
values(l_period_set_name||'-'||
l_pa_period_name||'-'||
l_instance||'-PPER', --cal_day_pk
'NA_EDW',
l_period_set_name||'-'||
l_pa_period_name||'-'||
l_instance||'-PA', --pa_period_fk
to_char(l_gl_start_date,'dd-mm-yyyy'), --day_fk
l_period_set_name||'-'||
l_period_name||'-'||l_instance, --cal_period_fk
'oracle_source', --ep_cal_period_fk
'',
l_instance,
l_pa_period_name||' ('||l_period_set_name||')', --name
'',
l_gl_start_date,
l_gl_end_date,
l_gl_end_date - l_gl_start_date + 1,
'',
'READY',
l_effective_period_num,
l_period_set_name,
l_period_type
);
Insert into EDW_TIME_CAL_DAY_LSTG
(CAL_DAY_PK,
FA_PERIOD_FK,
PA_PERIOD_FK,
DAY_FK,
CAL_PERIOD_FK,
EP_CAL_PERIOD_FK,
HOLIDAY_FLAG,
INSTANCE,
NAME,
WORK_DAY_FLAG ,
CALENDAR_DATE,
END_DATE,
TIMESPAN,
SEQ_NUMBER,
COLLECTION_STATUS,
EFFECTIVE_PERIOD_NUM,
PERIOD_SET_NAME,
PERIOD_TYPE
)
Values (
l_period_set_name||'-'||l_period_name||'-'||l_instance||'-CPER', -- CAL_DAY_PK
'NA_EDW', -- FA_PERIOD_FK
l_period_set_name||'-'||l_period_name||'-'||l_instance||'-GL', -- PA_PERIOD_FK
'NA_EDW', -- DAY_FK
l_period_set_name||'-'||l_period_name||'-'||l_instance, -- CAL_PERIOD_FK
'oracle_source', -- EP_CAL_PERIOD_FK
'',
l_instance,
l_period_name||' ('||l_period_set_name||')', --name
'', --work_day_flag
l_start_date, --calendar_date
l_end_date, --end_date
l_end_date - l_start_date + 1, --timespan
1, --seq_number
'READY', --collection_status
l_effective_period_num,
l_period_set_name,
l_period_type
);
Insert into EDW_TIME_PA_PERIOD_LSTG
(PA_PERIOD_PK,
CAL_PERIOD_FK,
INSTANCE,
PA_PERIOD,
NAME,
END_DATE,
START_DATE,
TIMESPAN,
COLLECTION_STATUS )
values(l_period_set_name||'-'||
l_pa_period_name||'-'||l_instance||'-PA', --pa_period_pk
l_period_set_name||'-'||
l_period_name||'-'||l_instance, --cal_period_fk
l_instance, --instance
l_pa_period_name, --pa_period
l_pa_period_name||' ('||l_period_set_name||')', --name
l_gl_end_date, --end_date
l_gl_start_date, --start_date
l_gl_end_date - l_gl_start_date + 1, --timespan
'READY'); --collection_status
Insert into EDW_TIME_PA_PERIOD_LSTG
(PA_PERIOD_PK,
CAL_PERIOD_FK,
INSTANCE,
PA_PERIOD,
NAME,
END_DATE,
START_DATE,
TIMESPAN,
COLLECTION_STATUS)
Values (
l_period_set_name||'-'||l_period_name||'-'||
l_instance||'-GL', --pa_period_pk
l_period_set_name||'-'||l_period_name||'-'||
l_instance, --cal_period_fk
l_instance, --instance
l_period_name, --pa_period
l_period_name||' ('||l_period_set_name||')', --name
l_end_date,
l_start_date,
l_end_date - l_start_date + 1,
'READY');
insert into edw_time_cal_period_lstg
(CAL_PERIOD_PK,
CAL_QTR_FK,
INSTANCE,
NAME,
CAL_PERIOD,
PERIOD_NAME,
END_DATE,
START_DATE,
TIMESPAN,
COLLECTION_STATUS)
values(l_period_set_name||'-'||l_period_name||'-'||l_instance, --cal_period_pk
l_period_set_name||'-'||l_period_type||'-'||l_period_year||
'-Q-'||l_quarter||'-'||l_instance, --cal_qtr_fk
l_instance,
l_period_name||' ('||l_period_set_name ||')', --name
l_period_name,
l_period_name,
l_gl_end_date,
l_gl_start_date,
l_gl_end_date - l_gl_start_date + 1,
'READY');
insert into EDW_TIME_CAL_QTR_LSTG
(CAL_QTR_PK ,
CAL_YEAR_FK ,
CAL_QTR,
INSTANCE,
NAME ,
END_DATE,
START_DATE,
TIMESPAN,
COLLECTION_STATUS )
values
(l_period_set_name||'-'||l_period_type||'-'||
l_period_year||'-Q-'||l_quarter||'-'||l_instance, --CAL_QTR_PK
l_period_set_name||'-'||l_period_type||'-'||
l_period_year||'-'||l_instance, --CAL_YEAR_FK
l_quarter_name||' '||l_quarter||', '||l_period_year||
' ('||l_period_set_name||')', --CAL_QTR
l_instance,
l_quarter_name||' '||l_quarter||', '||l_period_year||
' ('||l_period_set_name||')', --name
l_end_date,
l_start_date,
l_end_date - l_start_date + 1,
'READY');
insert into EDW_TIME_CAL_YEAR_LSTG
(
CAL_YEAR_PK ,
CAL_NAME_FK ,
CAL_YEAR,
INSTANCE,
NAME ,
END_DATE,
START_DATE,
TIMESPAN,
COLLECTION_STATUS
)
values(
l_period_set_name||'-'||l_period_type||'-'||
l_period_year||'-'||l_instance, -- cal_year_pk
l_period_set_name||'-'||l_period_type||'-'||l_instance, -- cal_name_fk
l_period_year, -- cal_year
l_instance,
l_period_year||' ('||l_period_set_name||')', --name
l_end_date,
l_start_date,
l_end_date - l_start_date + 1,
'READY' );
insert into EDW_TIME_CAL_NAME_LSTG(
CAL_NAME_PK ,
ALL_FK,
CAL_NAME,
CALENDAR_TYPE ,
DESCRIPTION ,
INSTANCE ,
NAME ,
end_date ,
timespan,
COLLECTION_STATUS )
Select distinct sob.period_set_name ||'-'||
sob.accounted_period_type||'-'||l_instance, --cal_name_pk
'ALL', --all_fk
sob.period_set_name, --cal_name
'Financial', --calendar_type
sets.description, --description
l_instance, --instance
sob.period_set_name, --name
sysdate, --end_date
1, --timespan
'READY' --collection_status
from gl_sets_of_books sob,
gl_period_sets sets
where sob.period_set_name = sets.period_set_name;
g_rows_inserted:=l_row_cday;
select period_set_name, period_type, instance_code
into l_period_set_name, l_period_type, l_master_instance
from edw_local_system_parameters;
INSERT INTO EDW_TIME_EP_CAL_PERIOD_LSTG
(CAL_PERIOD_PK,
CAL_QTR_FK,
INSTANCE,
NAME,
CAL_PERIOD,
PERIOD_NAME,
END_DATE,
START_DATE,
TIMESPAN,
COLLECTION_STATUS)
select period_name,
to_char(quarter_num)||'-'||to_char(period_year),
l_instance,
period_name,
period_name,
period_name,
end_date,
start_date,
end_date-start_date+1,
'READY'
FROM gl_periods
where period_set_name = l_period_set_name
and period_type = l_period_type
and adjustment_period_flag='N';
INSERT INTO EDW_TIME_EP_CAL_QTR_LSTG
(CAL_QTR_PK,
CAL_YEAR_FK,
CAL_QTR,
INSTANCE,
NAME,
END_DATE,
START_DATE,
TIMESPAN,
COLLECTION_STATUS)
Select to_char(quarter_num)||'-'||to_char(period_year), --CAL_QTR_PK
to_char(period_year), --CAL_YEAR_FK
l_quarter_name||' '||to_char(quarter_num)||', '||to_char(period_year), --CAL_QTR
l_instance, --INSTANCE
l_quarter_name||' '||to_char(quarter_num)||', '||to_char(period_year), --NAME
max(end_date), --END_DATE
min(start_date), --START_DATE
max(end_date)-min(start_date)+1, --TIMESPAN
'READY' --COLLECTION_STATUS
FROM gl_periods
where period_set_name=l_period_set_name
and period_type = l_period_type
and adjustment_period_flag='N'
group by period_year, quarter_num;
INSERT INTO EDW_TIME_EP_CAL_YEAR_LSTG
(CAL_YEAR_PK,
CAL_NAME_FK,
CAL_YEAR,
INSTANCE,
NAME,
END_DATE,
START_DATE,
TIMESPAN,
COLLECTION_STATUS)
Select to_char(period_year), --CAL_YEAR_PK
l_period_set_name, --CAL_NAME_FK
to_char(period_year), --CAL_YEAR
l_instance, --INSTANCE
to_char(period_year), --NAME
max(end_date), --END_DATE
min(start_date), --START_DATE
max(end_date)-min(start_date)+1, --TIMESPAN
'READY' --COLLECTION_STATUS
FROM gl_periods
where period_set_name=l_period_set_name
and period_type = l_period_type
and adjustment_period_flag='N'
group by period_year;
INSERT INTO EDW_TIME_EP_CAL_NAME_LSTG
(CAL_NAME_PK,
ALL_FK,
CAL_NAME,
CALENDAR_TYPE,
DESCRIPTION,
INSTANCE,
NAME,
end_date,
timespan,
COLLECTION_STATUS)
Select sets.period_set_name,
'ALL',
sets.period_set_name,
'Financial',
sets.description,
l_instance,
sets.period_set_name,
sysdate,
1,
'READY'
FROM gl_period_sets sets
WHERE sets.period_set_name = l_period_set_name;