Skip to Content

Select Query Help

Hi,

I trying to write a select query on MBEWH table. , The logic needs to find the first u2018previousu2019 month based on the given inputs.

For example,

LINV Count Date = 01/14/2008

MBEWH has entries for Month 6 Year 2008, price = 1.45

Month 3 Year 2008, price = 1.40

Month 12 Year 2007, price = 1.35

Month 11 Year 2007, price = 1.30

The logic needs to select Month 12 Year 2007 with price = 1.35 as it is nearest to LINV Count date.

I have written the below Query but not working as it is taking the month and year as individual values but it should take as combined year and month and should compare it with 200801.

MYEAR = WA_LINV-IDATU(4).

MMONT = WA_LINV-IDATU+4(2).

SELECT LFGJA LFMON VERPR PEINH FROM MBEWH INTO TABLE T_MBEWH WHERE

AND BWKEY = WA_LINV-WERKS

AND LFGJA < MYEAR

AND LFMON < MMONT.

I appreciate if someone can help in getting the correct solution.

Best Regards

Suresh

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 02, 2008 at 05:45 AM

    data: INPUT_PERIOD LIKE CKMLPP-POPER,

    INPUT_YEAR LIKE CKMLPP-BDATJ.

    CALL FUNCTION 'CKML_F_GET_PREVIOUS_PERIOD'

    EXPORTING

    INPUT_PERIOD = input_period

    INPUT_YEAR = input_year

    INPUT_PERIV = 'AL'

    IMPORTING

    PREVIOUS_PERIOD = input_period

    PREVIOUS_YEAR = input_year

    .

    if sy-subrc = 0.

    it_prev-spbup+0(4) = input_year.

    it_prev-spbup+4(2) = input_period.

    endif.

    Suresh, Check your INPUT_PERIV = 'AL'

    parameter, Dont exactly use this. Change the value. check the customized setup for this value in your system

    Go to T009c table and check the "Fiscal Input Variant values" t009c-periv values by clicking f4. give the appropriate value and put in this function module. now it will work.

    Regards,

    Santosh Kumar Mukka.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 01, 2008 at 03:19 PM

    Hi Suresh

    You can fix this simply by separating the logic to find the last month and the query on table MBEWH .

    First find the last month through a code logic Ex.

    MYEAR = WA_LINV-IDATU(4).

    MMONT = WA_LINV-IDATU+4(2) - 1 .

    If MMont = 0 .

    MMONT = 12 .

    MYEAR = MYEAR - 1.

    endif.

    SELECT LFGJA LFMON VERPR PEINH FROM MBEWH INTO TABLE T_MBEWH WHERE

    AND BWKEY = WA_LINV-WERKS

    AND LFGJA = MYEAR

    AND LFMON = MMONT.

    this should give you the last month's Material Valuation

    As I understand this what you need right ?

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 01, 2008 at 04:24 PM

    Hi,

    Try this code, this will meet your requirement,

    DATA : myear TYPE mbewh-lfgja,

    mmont TYPE mbewh-lfmon,

    myear1 TYPE mbewh-lfgja,

    mmont1 TYPE mbewh-lfmon.

    TYPES : BEGIN OF ty_mbewh,

    lfgja TYPE mbewh-lfgja,

    lfmon TYPE mbewh-lfmon,

    verpr TYPE mbewh-verpr,

    peinh TYPE mbewh-peinh,

    END OF ty_mbewh.

    DATA : it_mbewh TYPE TABLE OF ty_mbewh,

    wa_mbewh TYPE ty_mbewh.

    START-OF-SELECTION.

    myear = wa_linv-idatu+0(4).

    mmont = wa_linv-idatu+4(2).

    BREAK-POINT.

    SELECT

    lfgja

    lfmon

    verpr

    peinh FROM mbewh INTO TABLE it_mbewh

    WHERE bwkey EQ wa_linv-werks

    AND lfgja LT myear

    AND lfmon LT mmont.

    SORT it_mbewh DESCENDING BY lfgja lfmon.

    CLEAR wa_mbewh.

    READ TABLE it_mbewh INTO wa_mbewh INDEX 1.

    myear1 = wa_mbewh-lfgja.

    mmont1 = wa_mbewh-lfmon.

    CLEAR wa_mbewh.

    SORT it_mbewh DESCENDING BY lfgja lfmon.

    DELETE it_mbewh WHERE lfgja NE myear1 OR

    lfmon NE mmont1.

    -


    Internal table it_mbewh will have the data according to your condition. Reply if this code worked for you,

    Regards

    Bala Krishna

    Edited by: Bala Krishna on Sep 1, 2008 9:55 PM

    Edited by: Bala Krishna on Sep 2, 2008 10:36 AM

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 02, 2008 at 05:41 AM

    Hi Suresh,

    I am also doing the same object.

    You can try with this Function Module.

    data: INPUT_PERIOD LIKE CKMLPP-POPER,

    INPUT_YEAR LIKE CKMLPP-BDATJ.

    input_period = it_prev-spbup+4(2).

    input_year = it_prev-spbup+0(4).

    CALL FUNCTION 'CKML_F_GET_PREVIOUS_PERIOD'

    EXPORTING

    INPUT_PERIOD = input_period

    INPUT_YEAR = input_year

    INPUT_PERIV = 'AL'

    IMPORTING

    PREVIOUS_PERIOD = input_period

    PREVIOUS_YEAR = input_year

    .

    Regards,

    Santosh Kumar M.

    Add comment
    10|10000 characters needed characters exceeded