Skip to Content
Technical Articles
Author's profile photo Martin Grob

Extracting factory calendar data to a DSO in #SAP #BW

Introduction

In order to work with factory calendar information it is sometimes helpful if the information would be stored in a datastore object for each day factory calendar and company code if it is a working day or not. The information is extracted after the “factory calendar” have been replicated via “Transfer global settings”

Howto extract the factory calendar information

To achieve this two function modules are needed FISCPER_FROM_CALMONTH_CALC and DATE_CONVERT_TO_FACTORYDATE.

The target DSO contains those key fields / data fields. The data fields aside from the working days keyfigure are optional but might be useful aggregated views are of intrest.

/wp-content/uploads/2013/11/21_11_328408.png

The dataflow in this example forsees to load the required company code / factory calendar assignments from the enhanced masterdata object 0COMP_CODE. This can also be altered to suit e.g. 0PLANT and those factory calendar informations.

/wp-content/uploads/2013/11/21_111_328407.png

The attached expert routine determines for each company code and factory calendar ID if the individual calendarday is a working day or not. The information is stored in the DSO.

Result

/wp-content/uploads/2013/11/20_11_328406.png

Each day is either 0 (no working day) or 1 (working day).

Attached are both the FM and the required expert routine.

 

*$*$ begin of routine - insert your code only below this line        *-*
* --------- execution code ---------------------------

* load data fom 0CALDAY to internal table
    SELECT DATE0 SID FROM /BI0/SDATE INTO TABLE lt_calday
          WHERE DATE0 >= l_start_date AND
                 DATE0 <= l_end_date .

    LOOP AT SOURCE_PACKAGE assigning <SOURCE_FIELDS>.

* calculate cayday from SID table of 0CALDAY and assign
* corresponding fields

      LOOP AT lt_calday INTO ls_calday.
        IF ls_calday-sid >= l_start_date.

          RESULT_FIELDS-calday  = ls_calday-date0..
          RESULT_FIELDS-comp_code   = <SOURCE_FIELDS>-comp_code.
          RESULT_FIELDS-country  = <SOURCE_FIELDS>-country .
          RESULT_FIELDS-/BIC/ZWFCID  = <SOURCE_FIELDS>-/BIC/ZWFCID.
          RESULT_FIELDS-calyear  = RESULT_FIELDS-calday(4).
          RESULT_FIELDS-calmonth  = RESULT_FIELDS-calday(6).

* calculate FISCPER and FISCYEAR on CALDAY and CALMONTH
          IF <SOURCE_FIELDS>-fiscvarnt <> ''.
            CALL FUNCTION 'FISCPER_FROM_CALMONTH_CALC'
              EXPORTING
                iv_calmonth       = RESULT_FIELDS-calmonth
                iv_periv          = <SOURCE_FIELDS>-fiscvarnt
              IMPORTING
*          EV_FISCPER3       = RESULT_FIELDS-FISCPER
               ev_fiscyear       = l_fiscyear
               ev_fiscper        = l_fiscper
               EV_FISCPER3       = l_fiscper3.
            CALL FUNCTION 'DATE_GET_WEEK'
            EXPORTING
                DATE = ls_calday-date0
            IMPORTING
              WEEK = l_calweek.
* EXCEPTIONS
* DATE_INVALID = 1
* OTHERS = 2





          ENDIF.

          RESULT_FIELDS-fiscvarnt = <SOURCE_FIELDS>-fiscvarnt.
          RESULT_FIELDS-fiscyear = l_fiscyear.
          RESULT_FIELDS-fiscper = l_fiscper.
          RESULT_FIELDS-fiscper3 = l_fiscper3.
          RESULT_FIELDS-calweek = l_calweek.

* calculate working day from calday and factory calendar

          l_date = RESULT_FIELDS-calday.
          l_factory_calendar = RESULT_FIELDS-/BIC/ZWFCID.


          CALL FUNCTION 'Z_DATE_CHECK_WORKINGDAY'
            EXPORTING
              DATE                       = l_date
              FACTORY_CALENDAR_ID        = l_factory_calendar
              MESSAGE_TYPE               = 'S'
            IMPORTING
              I_WORKING_DAY              = l_working_day
            EXCEPTIONS
              DATE_AFTER_RANGE           = 1
              DATE_BEFORE_RANGE          = 2
              DATE_INVALID               = 3
              DATE_NO_WORKINGDAY         = 4
              FACTORY_CALENDAR_NOT_FOUND = 5
              MESSAGE_TYPE_INVALID       = 6
              OTHERS                     = 7.
          IF SY-SUBRC <> 0.
            MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
            WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
          ENDIF.

          RESULT_FIELDS-/BIC/ZWORDAY = l_working_day.

        ENDIF.

        APPEND RESULT_FIELDS to RESULT_PACKAGE.

      ENDLOOP.
    ENDLOOP.

*$*$ end of routine - insert your code only before this line         *-*

 

FUNCTION Z_DATE_CHECK_WORKINGDAY.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     VALUE(DATE) LIKE  SY-DATUM
*"     VALUE(FACTORY_CALENDAR_ID) LIKE  SCAL-FCALID
*"     VALUE(MESSAGE_TYPE) LIKE  SY-MSGTY
*"  EXPORTING
*"     VALUE(I_WORKING_DAY) TYPE  INT4
*"  EXCEPTIONS
*"      DATE_AFTER_RANGE
*"      DATE_BEFORE_RANGE
*"      DATE_INVALID
*"      DATE_NO_WORKINGDAY
*"      FACTORY_CALENDAR_NOT_FOUND
*"      MESSAGE_TYPE_INVALID
*"----------------------------------------------------------------------

  DATA: D10_STR(10)     TYPE C,
        WORKINGDAY_FLAG LIKE SCAL-INDICATOR.

  IF MESSAGE_TYPE CN 'IWEAS'.
    RAISE MESSAGE_TYPE_INVALID.
  ENDIF.

  IF FACTORY_CALENDAR_ID <> SPACE.
    WRITE DATE TO D10_STR DD/MM/YYYY.
    CALL FUNCTION 'DATE_CONVERT_TO_FACTORYDATE'
         EXPORTING
              DATE                 = DATE
              FACTORY_CALENDAR_ID  = FACTORY_CALENDAR_ID
         IMPORTING
              WORKINGDAY_INDICATOR = WORKINGDAY_FLAG
         EXCEPTIONS
              DATE_INVALID               = 1
              DATE_BEFORE_RANGE          = 2
              DATE_AFTER_RANGE           = 3
              FACTORY_CALENDAR_NOT_FOUND = 4.
    CASE SY-SUBRC.
      WHEN 1.                          " invalid date
        MESSAGE E205 WITH D10_STR
                RAISING DATE_INVALID.
      WHEN 2.                          " date before factory calendar
        MESSAGE I207 WITH D10_STR FACTORY_CALENDAR_ID
                RAISING DATE_BEFORE_RANGE.
      WHEN 3.                          " date past factory calendar
        MESSAGE I208 WITH D10_STR FACTORY_CALENDAR_ID
                RAISING DATE_AFTER_RANGE.
      WHEN 4.                          " Uknonw factory calendar
        MESSAGE I209 WITH FACTORY_CALENDAR_ID
                RAISING FACTORY_CALENDAR_NOT_FOUND.
    ENDCASE.


    IF SY-SUBRC = 0 AND
       WORKINGDAY_FLAG <> SPACE.       " no working day

*      MESSAGE ID 'T0' TYPE MESSAGE_TYPE NUMBER 206 WITH D10_STR
*              RAISING DATE_NO_WORKINGDAY.

* -----  Extention:

        I_WORKING_DAY = 0.   "  no working day
     ELSE.
        I_WORKING_DAY = 1.   "  is a working day

    ENDIF.
  ENDIF.

ENDFUNCTION.

Assigned Tags

      23 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Vikrant Mohite
      Vikrant Mohite

      Hi Martin,

      Snaps are broken.

      Can you please fix it

      Cheers,

      Vikrant.

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      thanks for the hint I'll fix them later today.

      Martin

      Author's profile photo Manna Das
      Manna Das

      Great work again Martin, thanks for sharing

      KR,

      Manna Das

      Author's profile photo Prashanth konduru
      Prashanth konduru

      one more good piece of info from Martin ... Thanks for sharing

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      you're welcome thanks!

      Author's profile photo CH Raman
      CH Raman

      Good doc and useful one. Are you loading to DSO by using master data source of comp code?

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      yes in our case we have a factory calendar per company code which is attached to 0COMP_CODE and we derive the working days from there. you could also do the same from 0PLANT where you have the factory calendar attached from bi content.

      Author's profile photo CH Raman
      CH Raman

      That sounds good. Thanks for the clarification Thank Martin

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      Hi Martin,

      This is innovative post from you. Seems to be nice code 🙂 . I suggest you to come up with such blogs. Thanks for sharing.

      If possible, please keep the code opened. I mean don't put in ZIp file.

      Regards,

      Suman

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      Hi Suman

      Thanks for your comment. I actually uploaded the texts as TXT but somehow it seems they get zipped automatically. 🙂

      Martin

      Author's profile photo Former Member
      Former Member

      Hey Martin,,,

      great blog. We also derive almost the same way 🙂

      Thanks for making such useful information on SCN.

      Author's profile photo Former Member
      Former Member

      Thanks for sharing .

      Regards,

      Sushant

      Author's profile photo abilash n
      abilash n

      Nice blog Martin as always....

      Author's profile photo Josh Reithofer
      Josh Reithofer

      Hi, nice Blog!

      Question: How did you got the (user input variables) l_start_date & l_end_date (see Expert routine) into this Expert routine (Source in ZIP is not complete).

      Plz. give exact required coding including the decleration of the variables & other steps/actions if required.

      By the way: By using 0Plant instead of 0Comp_code you get directly a value for the Calendar ID from the field 0Plant-Factcal_id. Which you can using when calling the custom FM in the expert routine. Here we used a custom datasource to add Company code (BUKRS) from ECC (R/3 6.0) to 0Plant. Which makes (BW-) live a bit easier here... 😉

      See also table T001W (* T001K / T001) in ECC & table TFACD (Factory Calendar) in ECC/BW.

      cheers, Josh

      Author's profile photo Former Member
      Former Member

      Brilliant blog thanks a lot, where is the attachment?

      Author's profile photo Simon Kußmann
      Simon Kußmann

      Hi Martin,

      I cannot find the attached as well. Could you help us here? 🙂

      Thanks!

      Author's profile photo Former Member
      Former Member

       

      Hi Martin,

      I cannot find the attached file. Could you help us here?

      this is my email shoker_eg1@hotmail.com

      Thanks!

      Author's profile photo Former Member
      Former Member

      Hi Martin,

      I cannot find the attached file. Can you help please?

       

      Thanks!

      Author's profile photo Christian Kunes
      Christian Kunes

      Hello Martin,

       

      Thanks for sharing.

       

      I can't find the attached file.Can you help us please ?

       

      Thanks in advance.

      Author's profile photo Judith Hansbauer
      Judith Hansbauer

      Hello,

      I would also like to see the routines, could anybody copy the code in here?

      Thanks in advance

      Judith

      Author's profile photo Mirko Alay
      Mirko Alay

      I can't see any of the attachments.  Were they removed?

      Author's profile photo Roger Heckly
      Roger Heckly

      Hello - the attachments are not available anymore - could you please add them again?

      Author's profile photo Martin Grob
      Martin Grob
      Blog Post Author

      I updated the blog post with the code snippets.. I'm surprised there is still interest on the subject