Generic FG/class for spreading non-monthly qty's into monthly buckets

[Former Member]

In custom long-term planning reports, we may have to use data like labor hours that are stored in non-monthly buckets, e.g. buckets like

12/15/2010 u2013 1/14/2011 100 hours

1/15/2011 u2013 2/14/2011 80 hours

etc.

But in order to make our long-term reports useful for cash-flow estimation/planning, we may have to reslot such non-monthly data into fiscal monthly buckets like:

Dec 2010

Jan 2011

Feb 2011

In the above simple example, the translation from non-monthly data to monthly data is simple:

half of the hours from the 12/15/2010-1/14/2011 non-monthly bucket go into the Dec 2010 monthly bucket and

half of the hours from the 12/15/2010-1/14/2001 non-monthly bucket go into the Jan 2011 monthly bucket.

etc.

And generalizing from this simple example, you can see that there will always be some ratio in which the non-monthly data can be split into monthly data. (For example, the ratio between a non-monthly bucket and two successive monthly buckets might be 3:1 if 3 weeks of the non-monthly bucket fall into the first monthly bucket and the fourth week of the non-monthly bucket falls into the second monthly bucket.

These considerations suggest that SAP developers and customers might benefit from a generic FG or class that:

accepted a set of non-monthly buckets plus a set of quantities

figured out the monthly buckets spanning this set of non-monthly buckets

spread the non-monthly data into the monthly buckets correctly (according to the simple u201Cratiou201D idea outlined above.)

This generic FG or class would do (1-3) regardless of the specific nature of the quantities involved ... because who cares what they are. Also, one could imagine a version of this class or FG where the output buckets might not be monthly, but rather a different set of non-monthly buckets than the input non-monthly buckets.

So, my four questions are:

Does anyone know of an SAP-delivered class or FG that does (1-3) (Iu2019m thinking SCM/APO might well have one u2026)

If not, does anyone have anything approaching this class of FG that they would care to share with me (via email, of course)?

If not, does any genius out there have the time to write such a class and put it in the SDN code base?

Do you all agree that the safest way to get the ratio is to take everything to the u201Clowest common denominatoru201D of days and then figure the ratio accordingly?

Thanks for considering this matter.

djh

[SimoneMilesi]

I just wrote a little report that i think i can help you in your task (convert it to a FM it's a simple task) that (i hope i understand your requirement): it split your buket on each month in a range of dates based on numeber of days.

REPORT ztest_split.
PARAMETERS: st_date LIKE sy-datum,
            end_date LIKE sy-datum,
            amount TYPE i.
DATA: BEGIN OF t_out OCCURS 0,
      year LIKE mkpf-mjahr,
      month(2) TYPE n,
      amount(15) TYPE p DECIMALS 2.
DATA: END OF t_out.
DATA: l_month(2) TYPE n,
      l_year(4) TYPE n,
      m_days TYPE i,
      total_days TYPE i,
      last_day LIKE sy-datum,
      out_flag.
START-OF-SELECTION.
  total_days = end_date - st_date.
  l_month = st_date+4(2).
  l_year = st_date(4).
  CLEAR out_flag.
  DO.
    IF l_month = st_date+4(2) AND l_year = st_date(4).
      CALL FUNCTION 'MM_LAST_DAY_OF_MONTHS'
        EXPORTING
          day_in            = st_date
        IMPORTING
          last_day_of_month = last_day
        EXCEPTIONS
          day_in_no_date    = 1
          OTHERS            = 2.
      m_days = last_day - st_date.
    ELSEIF l_month = end_date+4(2) AND l_year = end_date(4).

      m_days = end_date+6.
      MOVE 'X' TO out_flag.
    ELSE.
      CONCATENATE l_year l_month '01' INTO last_day.
      CALL FUNCTION 'MM_LAST_DAY_OF_MONTHS'
        EXPORTING
          day_in            = st_date
        IMPORTING
          last_day_of_month = last_day
        EXCEPTIONS
          day_in_no_date    = 1
          OTHERS            = 2.
      m_days = last_day+6.

    ENDIF.

    MOVE: l_month TO t_out-month,
          l_year TO  t_out-year.
    t_out-amount = amount / total_days * m_days.
    APPEND t_out. CLEAR t_out.


    IF l_month EQ 12.
      ADD 1 TO l_year.
      CLEAR l_month.
    ENDIF.
    ADD 1 TO l_month.
    IF out_flag EQ 'X'.
      EXIT.
    ENDIF.
  ENDDO.

Give a try and let me know

[former_member181923]

Hi Simone -

Thanks so much ... that was very nice of you to do so promptly ! In fact, I feel a little guilty ... like Tom Sawyer looking at the fence that you just helped me paint ....! (If you don't know the novel "Tom Sawyer", it's by the American novelist Mark Twain ... kind of a children's novel but also very much an adult movel.)

Anyway, I'm assuming your code is going to work so I have awarded 10 and marked the question answered.

But I will respond back to the thread after I've made the code loop on a set of buckets and packaged it in a class or FG ... just so you know everything worked out OK ....

Best regards

djh

[SimoneMilesi]

I know Twain, i read many times Tom Sawyer

Don't feel guilty, i like to solve things

If you need some help or any other hint about my code, let me know (you can find my e-mail into my profile if you prefere).

[former_member181923]

Hi Simone -

Just "circling" back here with two chunks of completed working code (see below). The first chunk is a function module based on the code you provided - the syntax has been modernized (no wa's and no headers), and the code has been tucked inside an envelope which allows the monthly totals to accumulate within a given span of months (over repetitive calls to the function module.) The second chunk is a form which calls the function module, to show how the function module would be used in "real life" when qty's in non-monthly periods have to be bucketed to months.

Thanks again for your kind help on this ...

Best

djh

Edited by: David Halitsky on Sep 9, 2010 9:41 PM

[former_member181923]

As you can see from below, the code markers aren't working in Mozilla either ....oh well

FUNCTION ZCK_TRAN_TO_MONTHS.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     REFERENCE(START_DATE) TYPE  FSTAD
*"     REFERENCE(END_DATE) TYPE  FENDD
*"     REFERENCE(AMOUNT) TYPE  ZZVGWRT
*"  TABLES
*"      MONTH_TABLE_FOR_RUN TYPE  ZMMY_MSTR_DATA_EST_YRMO
*"  CHANGING
*"     REFERENCE(MONTH_BUCKETS) TYPE  ZMMS_MSTR_DATA_EST_MOBUCKETS
*"----------------------------------------------------------------------
FIELD-SYMBOLS:
  <fs_mobu>             TYPE zmms_mstr_data_est_mobuckets,
  <fs_mota>             TYPE zmms_mstr_data_est_yrmo,
  <fs_buck>             TYPE zzvgwrt.                        "bucket of month_buckets

DATA:
  lv_month(2)           TYPE n,
  lv_year(4)            TYPE n,
  lv_m_days             TYPE i,
  lv_total_days         TYPE i,
  lv_last_day           LIKE sy-datum,
  lv_out_flag           TYPE c,

  ls_month_buckets      TYPE zmms_mstr_data_est_mobuckets,
  lv_yrmo               TYPE zzbkper,
  lv_index1             LIKE sy-tabix,
  lv_index2             LIKE sy-tabix,
  lt_month_table        TYPE zmmy_mstr_data_est_yrmo.

  lt_month_table[] = month_table_for_run[].
  lv_total_days    = end_date - start_date.
  lv_month         = start_date+4(2).
  lv_year          = start_date(4).
  CLEAR:             lv_out_flag,
                     gt_mo_spread,
                     ls_month_buckets.

  DO.
    IF lv_month = start_date+4(2) AND lv_year = start_date(4).
      CALL FUNCTION 'MM_LAST_DAY_OF_MONTHS'
        EXPORTING
          day_in            = start_date
        IMPORTING
          last_day_of_month = lv_last_day
        EXCEPTIONS
          day_in_no_date    = 1
          OTHERS            = 2.
      lv_m_days = lv_last_day - start_date.

    ELSEIF lv_month = end_date+4(2) AND lv_year = end_date(4).

      lv_m_days = end_date+6.
      MOVE 'X' TO lv_out_flag.
    ELSE.
      CONCATENATE lv_year lv_month '01' INTO lv_last_day.
      CALL FUNCTION 'MM_LAST_DAY_OF_MONTHS'
        EXPORTING
          day_in            = start_date
        IMPORTING
          last_day_of_month = lv_last_day
        EXCEPTIONS
          day_in_no_date    = 1
          OTHERS            = 2.
      lv_m_days = lv_last_day+6.

    ENDIF.

    APPEND INITIAL LINE TO gt_mo_spread ASSIGNING <fs_mosp>.
    <fs_mosp>-year   = lv_year.
    <fs_mosp>-month  = lv_month.
    <fs_mosp>-amount = amount / lv_total_days * lv_m_days.

    IF lv_month EQ 12.
      ADD 1 TO lv_year.
      CLEAR lv_month.
    ENDIF.
    ADD 1 TO lv_month.
    IF lv_out_flag EQ 'X'.
      EXIT.
    ENDIF.

  ENDDO.

* now:
*   loop thru gt_mo_spread
*   pick up month_bucket index from month_table_for_run
*   add amount from gt_mo_spread row to appropriate month_bucket

  ASSIGN ls_month_buckets TO <fs_mobu>.
  LOOP AT gt_mo_spread ASSIGNING <fs_mosp>.

    lv_index1 = sy-tabix.                          "just in case we need it
    CONCATENATE <fs_mosp>-year
                <fs_mosp>-month
           INTO lv_yrmo.
    READ TABLE lt_month_table ASSIGNING <fs_mota>
      WITH KEY
        zzyrmo = lv_yrmo.
    lv_index2  = sy-tabix.
    ASSIGN COMPONENT lv_index2 OF STRUCTURE <fs_mobu> TO <fs_buck>.
    <fs_buck> = <fs_buck> + <fs_mosp>-amount.

  ENDLOOP.

  month_buckets = ls_month_buckets.

ENDFUNCTION.

FORM build_bucketed_detail.

FIELD-SYMBOLS:
  <fs_mobu>                         TYPE zmms_mstr_data_est_mobuckets,
  <fs_comp1>                        TYPE zzvgwrt,  "bucket in ls_mo_buckets/<fs_mobu>
  <fs_comp2>                        TYPE zzvgwrt,
  <fs_mota>                         TYPE zmms_mstr_data_est_yrmo,
  <fs_buck>                         TYPE zzvgwrt.            "bucket of month_buckets

DATA:
  lv_matnr_arbid_cur(26)            TYPE c,
  lv_matnr_arbid_lag(26)            TYPE c,
  lv_dtlh_index                     LIKE sy-tabix,
  ls_month_buckets                  TYPE zmms_mstr_data_est_mobuckets,
  lv_nummo                          TYPE i,

  lv_yrmo                           TYPE zzbkper,
  lv_yindx                          TYPE i,

  lv_bindx1                         LIKE sy-tabix,     "index on ls_month_buckets cmpnts
  lv_bindx2                         LIKE sy-tabix.     "index on <fs_dtlhmo> buckets
                                                       "note: must be lv_bindx1 + 5.

  lv_nummo = LINES( gt_mo_tbl ).

  READ TABLE gt_dtlh_keys INDEX 1 ASSIGNING <fs_dtlh>.
    CONCATENATE <fs_dtlh>-matnr
                <fs_dtlh>-arbid
           INTO lv_matnr_arbid_lag.
  lv_dtlh_index = 1.

  APPEND INITIAL LINE TO gt_dtlhmo_keys ASSIGNING <fs_dtlhmo>.
  <fs_dtlhmo>-matnr      = <fs_dtlh>-matnr.
  <fs_dtlhmo>-arbid      = <fs_dtlh>-arbid.
  <fs_dtlhmo>-arbpl      = <fs_dtlh>-arbpl.
  <fs_dtlhmo>-kostl      = <fs_dtlh>-kostl.

  ASSIGN ls_month_buckets TO <fs_mobu>.

  DO.

    READ TABLE gt_dtlh_keys INDEX lv_dtlh_index ASSIGNING <fs_dtlh>.

    IF sy-subrc <> 0.
      EXIT.
    ENDIF.

    CONCATENATE <fs_dtlh>-matnr
                <fs_dtlh>-arbid
           INTO lv_matnr_arbid_cur.
    IF lv_matnr_arbid_lag <> lv_matnr_arbid_cur.

      CLEAR <fs_dtlhmo>.
      APPEND INITIAL LINE TO gt_dtlhmo_keys ASSIGNING <fs_dtlhmo>.
      <fs_dtlhmo>-matnr        = <fs_dtlh>-matnr.
      <fs_dtlhmo>-arbid        = <fs_dtlh>-arbid.
      <fs_dtlhmo>-arbpl        = <fs_dtlh>-arbpl.
      <fs_dtlhmo>-kostl        = <fs_dtlh>-kostl.

      CLEAR ls_month_buckets.
      CLEAR <fs_mobu>.

    ENDIF.

    <fs_dtlhmo>-tot_vgw01_03   = <fs_dtlh>-vgw01 +
                                 <fs_dtlh>-vgw02 +
                                 <fs_dtlh>-vgw03.

    IF <fs_dtlh>-fstad+4(02) = <fs_dtlh>-fendd+4(02).

      lv_yrmo = <fs_dtlh>-fstad+0(6).
      READ TABLE gt_mo_tbl ASSIGNING <fs_mota>
        WITH KEY
          zzyrmo = lv_yrmo.
      lv_yindx  = sy-tabix.                                "month number
      lv_yindx  = lv_yindx  + 5.                           "to account for leading fields
      ASSIGN COMPONENT lv_yindx
          OF STRUCTURE <fs_dtlhmo>
                    TO <fs_buck>.
      <fs_buck> = <fs_buck> + <fs_dtlh>-tot_vgw01_03.

    ELSE.

      CALL FUNCTION 'ZCK_TRAN_TO_MONTHS'
        EXPORTING
          START_DATE             = <fs_dtlh>-fstad
          END_DATE               = <fs_dtlh>-fendd
          AMOUNT                 = <fs_dtlhmo>-tot_vgw01_03
        TABLES
          MONTH_TABLE_FOR_RUN    = gt_mo_tbl
        CHANGING
          MONTH_BUCKETS          = ls_month_buckets.

      lv_bindx1 = 1.
      DO.
        IF lv_bindx1 > lv_nummo.
          EXIT.
        ENDIF.
        lv_bindx2 = lv_bindx1 + 5.
        ASSIGN COMPONENT lv_bindx1 OF STRUCTURE <fs_mobu>   TO <fs_comp1>.
        ASSIGN COMPONENT lv_bindx2 OF STRUCTURE <fs_dtlhmo> TO <fs_comp2>.
        <fs_comp2> = <fs_comp2> + <fs_comp1>.

        lv_bindx1 = lv_bindx1 + 1.

      ENDDO.

    ENDIF.

    lv_dtlh_index = lv_dtlh_index + 1.

  ENDDO.

ENDFORM.

Edited by: David Halitsky on Sep 9, 2010 9:42 PM