--自动生成当前年份所有月的会计期间
--Parameters:
--:YEAR 当前年份
--:TYPE 会计日历类型
SELECT INITCAP (TO_CHAR (TO_DATE (ROWNUM, 'MM'), 'MON')) PREFIX,
:TYPE TYPE,
:YEAR YEAR,
TO_CHAR (TO_DATE (ROWNUM, 'MM'), 'q') QUARTER,
ROWNUM NUM,
( :YEAR || '-' || ROWNUM || '-01') FROM_DAY,
TO_CHAR (LAST_DAY (TO_DATE ( :YEAR || '-' || ROWNUM, 'YYYY-MM')), 'YYYY-MM-DD') TO_DAY,
INITCAP (TO_CHAR (TO_DATE (ROWNUM || '-' || :YEAR, 'MM-YYYY'), 'MON-YYYY')) NAME
FROM DUAL
CONNECT BY ROWNUM <= 12
UNION ALL
SELECT 'Adj' PREFIX,
:TYPE TYPE,
:YEAR YEAR,
'4' QUARTER,
13 NUM,
TO_CHAR (LAST_DAY (TO_DATE ( :YEAR || '-' || '12', 'YYYY-MM')), 'YYYY-MM-DD') FROM_DAY,
TO_CHAR (LAST_DAY (TO_DATE ( :YEAR || '-' || '12', 'YYYY-MM')), 'YYYY-MM-DD') TO_DAY,
'Adj-' || :YEAR NAME
FROM DUAL;
--Dataload template
SELECT INITCAP (TO_CHAR (TO_DATE (ROWNUM, 'MM'), 'MON')) PREFIX,
'TAB' KEY,
:TYPE TYPE,
'TAB' KEY,
:YEAR YEAR,
'TAB' KEY,
TO_CHAR (TO_DATE (ROWNUM, 'MM'), 'q') QUARTER,
'TAB' KEY,
ROWNUM NUM,
'TAB' KEY,
( :YEAR || '-' || ROWNUM || '-01') FROM_DAY,
'TAB' KEY,
TO_CHAR (LAST_DAY (TO_DATE ( :YEAR || '-' || ROWNUM, 'YYYY-MM')), 'YYYY-MM-DD') TO_DAY,
'TAB' KEY,
INITCAP (TO_CHAR (TO_DATE (ROWNUM || '-' || :YEAR, 'MM-YYYY'), 'MON-YYYY')) NAME,
'TAB' KEY,
'^{DOWN}' KEY
FROM DUAL
CONNECT BY ROWNUM <= 12
UNION ALL
SELECT 'Adj' PREFIX,
'TAB' KEY,
:TYPE TYPE,
'TAB' KEY,
:YEAR YEAR,
'TAB' KEY,
'4' QUARTER,
'TAB' KEY,
13 NUM,
'TAB' KEY,
TO_CHAR (LAST_DAY (TO_DATE ( :YEAR || '-' || '12', 'YYYY-MM')), 'YYYY-MM-DD') FROM_DAY,
'TAB' KEY,
TO_CHAR (LAST_DAY (TO_DATE ( :YEAR || '-' || '12', 'YYYY-MM')), 'YYYY-MM-DD') TO_DAY,
'TAB' KEY,
'Adj-' || :YEAR NAME,
'TAB' KEY,
'*SB' KEY
FROM DUAL;