Skip to Content

There had been always a requirement to create a HANA Information model object to display the Factory Calendar. I just happened to receive some Information on Factory calendar related tables and could create a HANA script for displaying the Factory Calendar. This can be customized and used in a script based Calc view and can be then joined with the Transactional data.

Please note that the script is currently created to suit my own business requirements and can be enhanced to add more information as per your business needs.

Tables required for Factory Calendar HANA Information model:

  • SAP Tables:
    • TFACD – Factory calendar definition
    • TFACT – Factory calendar texts
    • TFAIN – Calendar: Intervals for company holidays, special shifts
    • THOC – Public Holiday Calendar
    • THOL – Public Holidays
    • THOLT – Public holiday text
  • HANA generated Table:
    • “_SYS_BI”.M_TIME_DIMENSION
  • User defined Table:
    • TC_DAY_CALENDAR

I am sure there is proper documentation on Factory Calendar related tables on SAP Help portal which can be helpful in understanding the table structures.

The HANA generated Table M_TIME_DIMENSION contains data as generated using “Generate Time Data” option in HANA. Please find a nice document on this forum explaining how to generate the data. (http://scn.sap.com/docs/DOC-25977).

The granularity of the data in the generated table is quite high at “Second” level leading to millions of records in the table and may not be required for the Factory calendar. Hence a user defined table (TC_DAY_CALENDAR) can be created as a subset of the data from M_TIME_DIMENSION.

*** This can also be a view on M_TIME_DIMENSION, but I chose to create a table for some other purpose.

The code for the User defined table and the factory Calendar script is as follows:

CREATE COLUMN TABLE TC_DAY_CALENDAR (

DATE_SQL          DATE,

DATE_SAP          VARCHAR(8),

YEAR              VARCHAR(4),

QUARTER           VARCHAR(2),

MONTH             VARCHAR(2),

WEEK              VARCHAR(2),

WEEK_YEAR         VARCHAR(4),

DAY_OF_WEEK       VARCHAR(2),

DAY               VARCHAR(2),

CALQUARTER        VARCHAR(5),

CALMONTH          VARCHAR(6),

CALWEEK           VARCHAR(6),

YEAR_INT          INTEGER,

QUARTER_INT       TINYINT,

MONTH_INT         TINYINT,

WEEK_INT          TINYINT,

WEEK_YEAR_INT     INTEGER,

DAY_OF_WEEK_INT   TINYINT,

DAY_INT           TINYINT,

MONTH_LAST_DAY    TINYINT

);

insert into TC_DAY_CALENDAR

  ( DATE_SQL, DATE_SAP, YEAR, QUARTER, MONTH, WEEK, WEEK_YEAR,

    DAY_OF_WEEK, DAY, CALQUARTER, CALMONTH, CALWEEK,

    YEAR_INT, QUARTER_INT, MONTH_INT, WEEK_INT, WEEK_YEAR_INT,

    DAY_OF_WEEK_INT, DAY_INT, MONTH_LAST_DAY )

select distinct

    DATE_SQL, DATE_SAP, YEAR, QUARTER, MONTH, WEEK, WEEK_YEAR,

    DAY_OF_WEEK, DAY, CALQUARTER, CALMONTH, CALWEEK,

    YEAR_INT, QUARTER_INT, MONTH_INT, WEEK_INT, WEEK_YEAR_INT,

    DAY_OF_WEEK_INT, DAY_INT, MONTH_LAST_DAY

from “_SYS_BI”.M_TIME_DIMENSION

where HOUR = ’12’; — Required as for some of the dates there could be another Week number assigned, especially for Hour ’00’.

Script for Factory Calendar:

SELECT

  tfacd.ident FACT_CAL_ID,

  tfact.ltext FACT_CAL_NAME,

  cal.DATE_SQL,

  cal.DATE_SAP,

  cal.YEAR,

  cal.QUARTER,

  cal.MONTH,

  cal.WEEK,

  cal.WEEK_YEAR,

  cal.DAY_OF_WEEK,

  cal.DAY,

  cal.CALQUARTER,

  cal.CALMONTH,

  cal.CALWEEK,

  cal.YEAR_INT,

  cal.QUARTER_INT,

  cal.MONTH_INT,

  cal.WEEK_INT,

  cal.WEEK_YEAR_INT,

  cal.DAY_OF_WEEK_INT,

  cal.DAY_INT,

  cal.MONTH_LAST_DAY,

  DAYNAME(cal.DATE_SQL) DAY_NAME,

  MONTHNAME(cal.DATE_SQL) MONTH_NAME,

  CASE

    WHEN tfain.wert = 0 THEN 0

    WHEN tfain.wert = 1 THEN 1

    WHEN phol.IS_HOLIDAY =’X’ AND tfacd.fetag = 0 THEN 0

    WHEN cal.DAY_OF_WEEK_INT = 0 AND tfacd.motag = 0 THEN 0

    WHEN cal.DAY_OF_WEEK_INT = 1 AND tfacd.ditag = 0 THEN 0

    WHEN cal.DAY_OF_WEEK_INT = 2 AND tfacd.miwch = 0 THEN 0

    WHEN cal.DAY_OF_WEEK_INT = 3 AND tfacd.dotag = 0 THEN 0

    WHEN cal.DAY_OF_WEEK_INT = 4 AND tfacd.frtag = 0 THEN 0

    WHEN cal.DAY_OF_WEEK_INT = 5 AND tfacd.satag = 0 THEN 0

    WHEN cal.DAY_OF_WEEK_INT = 6 AND tfacd.sotag = 0 THEN 0

    ELSE 1

  END WORKDAY,

  phol.DATE_DESC_LONG HOLIDAY_DESC,

  –IFNULL(phol.DATE_DESC_LONG, DAYNAME(cal.DATE_SQL)) HOLIDAY_DESC,

  CASE

    WHEN cal.DATE_SQL

      = MAX(CASE WHEN tfain.wert = 0 THEN NULL

                 WHEN tfain.wert = 1 THEN NULL

                 WHEN phol.IS_HOLIDAY =’X’ AND tfacd.fetag = 0 THEN NULL

                 WHEN cal.DAY_OF_WEEK_INT = 0 AND tfacd.motag = 0 THEN NULL

                 WHEN cal.DAY_OF_WEEK_INT = 1 AND tfacd.ditag = 0 THEN NULL

                 WHEN cal.DAY_OF_WEEK_INT = 2 AND tfacd.miwch = 0 THEN NULL

                 WHEN cal.DAY_OF_WEEK_INT = 3 AND tfacd.dotag = 0 THEN NULL

                 WHEN cal.DAY_OF_WEEK_INT = 4 AND tfacd.frtag = 0 THEN NULL

                 WHEN cal.DAY_OF_WEEK_INT = 5 AND tfacd.satag = 0 THEN NULL

                 WHEN cal.DAY_OF_WEEK_INT = 6 AND tfacd.sotag = 0 THEN NULL

                 ELSE cal.DATE_SQL

            END) over (partition BY cal.CALMONTH)

    THEN 1

    ELSE 0

  END last_workday_in_month

FROM TC_DAY_CALENDAR cal

INNER JOIN SAPUSER.TFACD tfacd ON cal.DATE_SAP BETWEEN (tfacd.vjahr|| ‘0101’) AND (tfacd.bjahr|| ‘1231’)

LEFT OUTER JOIN SAPUSER.TFACT tfact ON tfact.ident  = tfacd.ident AND tfact.spras = ‘E’

LEFT OUTER JOIN SAPUSER.TFAIN tfain ON tfain.ident = tfacd.ident

                        AND cal.DATE_SAP BETWEEN (tfain.von) AND (tfain.bis)

LEFT OUTER JOIN V_PUBLIC_HOLIDAY phol ON phol.DATE = cal.DATE_SAP AND phol.IDENT = tfacd.hocid

–WHERE tfacd.ident = ‘D1’ — Specify factory Calendar Id

—  AND cal.DATE_SAP BETWEEN ‘20130701’ and ‘20130801’  — Date Range

–ORDER BY cal.DATE_SAP

;

A subset of the above data can be seen in the below screenshot:

Factory_Cal_output.JPG

Hope this helps.

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Prachi Tiwari

    Hi Ravindra,

    can you please explain why are you joining TC_DAY_CALENDAR with V_PUBLIC_HOLIDAY? And what actually is there in V_PUBLIC_HOLIDAY?

    Prachi

    (0) 
    1. Ravindra Channe Post author

      Hi Prachi, Nick,

      I think I missed the script for V_PUBLIC_HOLIDAY 😛 . Please find it below

      CREATE VIEW V_PUBLIC_HOLIDAY (IDENT, DATE, DATE_DESC_LONG, IS_HOLIDAY) as

        SELECT ident, datum, ltext, ‘X’

        FROM SAPSHA.THOC thoc

        LEFT JOIN SAPSHA.THOLT tholt ON thoc.ftgid = tholt.ftgid

        AND tholt.spras = ‘E’

      ;

      This is used to identify if the date is a holiday or not. It need not be a predefined view, it can also be put in the query itself as an in-line view (use the select statement directly with an alias).

      Regards,

      P.S. This is an year old post, I am sure there are optimized ways to implement the same now.

      Ravi

      (0) 
  2. David Desmonds

    Dear Ravindra,

    Thanks for your post.

    I run your script on HANA Studio 2.0.19 release – I have the following error message

    ” No measure defined in a reporting enabled view “.

    Could you explain me how to define the measure ?

    Regards

    Ken

    (0) 
  3. Roger Sainsbury

    This Blog helped me a lot, thanks Ravindra. In my case I needed an ABAP-based CDS view for the Factory calendar, so I adapted the HANA Calc view script given to a DDL equivalent shown below. Note I’ve kept it as simple as possible – the view only contains the Factory Calendar ID, the Calendar Date and a Working-day indicator. There are some some optional parameters for Factory Calendar ID and Date from/to which are currently commented out.

    Roger

    @AbapCatalog.sqlViewName: 'ZFACCALSQL'
    @AbapCatalog.compiler.compareFilter: true
    @AccessControl.authorizationCheck: #NOT_REQUIRED
    @EndUserText.label: 'Identifies working vs non-working days'
    define view Z_Factory_Calendar 
    
    /*
    with parameters
      p_fac_cal      :wfcid,
      p_date_low     :abap.dats,
      p_date_high    :abap.dats
    */
    as 
    
        select from I_CalendarDate as cal
        
     // Factory Calendar determines which days of the week are workdays   
        inner join tfacd 
          on cal.CalendarYear between tfacd.vjahr and tfacd.bjahr
          
     // Special rules for 1-off calendar changes, such as a Factory outage
        left outer join tfain 
          on tfain.ident = tfacd.ident 
         and cal.CalendarDate between tfain.von and tfain.bis
        
     // Each Factory Calendar has a Public Holiday Calendar assigned to it
        left outer join thoc
          on thoc.datum     = cal.CalendarDate 
         and thoc.ident     = tfacd.hocid
        
    {
          key tfacd.ident       as FactoryCalendarID,
           
          key cal.CalendarDate,
        
          case
    
    //      Special changes take precedence    
            when tfain.wert = '0' then 0
        
            when tfain.wert = '1' then 1
        
    //      Then public holidays    
            when thoc.datum is not null and tfacd.fetag = '0' then 0
        
    //      Lastly the day of the week
            when cal.WeekDay = '1' and tfacd.motag = '0' then 0
        
            when cal.WeekDay = '2' and tfacd.ditag = '0' then 0
        
            when cal.WeekDay = '3' and tfacd.miwch = '0' then 0
        
            when cal.WeekDay = '4' and tfacd.dotag = '0' then 0
        
            when cal.WeekDay = '5' and tfacd.frtag = '0' then 0
        
            when cal.WeekDay = '6' and tfacd.satag = '0' then 0
        
            when cal.WeekDay = '7' and tfacd.sotag = '0' then 0
        
    //      If none of the above make it a non-working day, then it's a work day
            else 1
        
          end as WorkDay    
    }
    //    where tfacd.ident = :p_fac_cal                               -- Specify factory Calendar Id
    //      and cal.CalendarDate between :p_date_low and :p_date_high  -- Date Range

     

    (0) 

Leave a Reply