Select Query Help

[former_member569226]

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

[Former Member]

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 ?

[former_member585060]

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

[Former Member]

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.

[Former Member]

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.