EBS R12 生成会计日历

-- 会计日历  
-- GL>>Setup>>Financials>>Calendars>>Accounting;
-- Prefix type year Quarter Num From To Name 
-- 05 Month 2020 2 5  01-MAY-2020 31-MAY-2020  05-20 
 WITH CTE AS (
   select add_months(to_date('2021-01-01','yyyy-mm-dd') ,rownum-1) as "DATETIME"
     FROM DUAL CONNECT BY ROWNUM <= 12 
 ) 
 SELECT TO_CHAR(DATETIME,'MM') AS "PREFIX",
        'Month' as "TYPE",
        TO_CHAR(DATETIME,'YYYY') AS "YEAR",
        TO_CHAR(DATETIME, 'Q') AS "QUARTER",
        TO_NUMBER(TO_CHAR(DATETIME,'MM')) AS "NUM",
        TO_CHAR(DATETIME,'DD-MON-YYYY') AS "FROM",
        TO_CHAR(LAST_DAY(DATETIME),'DD-MON-YYYY') AS "TO",
        TO_CHAR(DATETIME,'MM-YY') AS "NAME"
   FROM CTE 
 
 

 

以下通过 SQL server 生成会计日历资料；

会计日历

N:GL>>设置>>财务系统>>日历>>会计；

通过SQL Server 2008r2环境生成以上图所示的格式资料，然后通过DATALOAD导入 ；

 

--会计日历

 with ymd as

 ( select dateadd(month,number,'2013-01-01') as date1

  from master..spt_values

 where type='P' 

  and number between 0 and 119 )

select convert(NVARCHAR(2), DATE1,110) as [Prefix], -- 前綴

       'Calendar Month' as [Type], -- 類型

       convert(NVARCHAR(4), DATE1,120) as [Year],

       datepart(quarter,date1) as [Quarter],

       datepart(month,date1) as [Num],

       convert(nvarchar(10),date1, 105) as [From],

       CONVERT(NVARCHAR(10),dbo.fn_last_day(date1), 105) as [To],

       substring(convert(nvarchar(10), ymd.date1 ,5) , 4,10) as [Name]

  from ymd 

从2013年1月到 2022年12月；

 

注意：SQL Server 2008R2没有ORACLE 的LAST_DAY()函数，

FN_LAST_DAY(）为自定义的用户函数；功能同LAST_DAY();