Make Report Aging Stock

[former_member540768]

hai guys,

please help me, i need to get output:

1. plant = werks
2. plant name = name
3. Material = matnr
4. Sku = bismt
5. Description = maktx
6. Sloc = lgort
7. Stock Qty = sum (clabs)
8. Avg cost = sum( lbkum)
9. Stock value = sum (clabs) * avgcost
10. last recv = max (budat_mkpf)
11. aging = the difference between max(budat_mkpf ) and current date, how many days
12. batch = charg
13. expired date = vfdat

REPORT ZPM_REPORT_AGING_STOCK.
TYPE-POOLS : SLIS.

TABLES : MCHB, MAKT, T001W, MARA, MSEG, MBEW, MCHA.

TYPES : BEGIN OF TY_RESULT,
WERKS LIKE MCHB-WERKS, "PLANT
NAME1 LIKE T001W-NAME1, "PLANT NAME
MATNR LIKE MARA-MATNR, "MATERIAL
BISMT LIKE MARA-BISMT, "SKU
MAKTX LIKE MAKT-MAKTX, "DESCRIPTION
LGORT LIKE MCHB-LGORT, "SLOC
SUM_CLABS LIKE MCHB-CLABS, "STOCK QTY
MAX_BUDAT_MKPF LIKE MSEG-BUDAT_MKPF, "LAST RECV
CHARG LIKE MCHB-CHARG, "BATCH
VFDAT LIKE MCHA-VFDAT, "EXPIRED DATE
SALK3 LIKE MBEW-SALK3, "
SUM_LBKUM LIKE MBEW-LBKUM, "
END OF TY_RESULT.

TYPES : BEGIN OF TY_T0MB,
BWKEY LIKE T001W-BWKEY,
LBKUM LIKE MBEW-LBKUM,
SALK3 LIKE MBEW-SALK3,
END OF TY_T0MB.

TYPES : BEGIN OF TY_MCT0,
WERKS LIKE MCHB-WERKS,
MATNR LIKE MCHB-MATNR,
LGORT LIKE MCHB-LGORT,
CLABS LIKE MCHB-CLABS,
CHARG LIKE MCHB-CHARG,
NAME1 LIKE T001W-NAME1,
END OF TY_MCT0.

TYPES : BEGIN OF TY_MCHB,
WERKS LIKE MCHB-WERKS,
MATNR LIKE MCHB-MATNR,
LGORT LIKE MCHB-LGORT,
CLABS LIKE MCHB-CLABS,
CHARG LIKE MCHB-CHARG,
END OF TY_MCHB.

TYPES : BEGIN OF TY_MAKT,
MAKTX LIKE MAKT-MAKTX,
MATNR LIKE MAKT-MATNR,
END OF TY_MAKT.

TYPES : BEGIN OF TY_T001W,
BWKEY LIKE T001W-BWKEY,
WERKS LIKE T001W-WERKS,
NAME1 LIKE T001W-NAME1,
END OF TY_T001W.

TYPES : BEGIN OF TY_MARA,
BISMT LIKE MARA-BISMT,
MTART LIKE MARA-MTART,
MATNR LIKE MARA-MATNR,
END OF TY_MARA.

TYPES : BEGIN OF TY_MSEG,
BUDAT_MKPF LIKE MSEG-BUDAT_MKPF,
MATNR LIKE MSEG-MATNR,
WERKS LIKE MSEG-WERKS,
BWART LIKE MSEG-BWART,
END OF TY_MSEG.

TYPES : BEGIN OF TY_MCHA,
VFDAT LIKE MCHA-VFDAT,
MATNR LIKE MCHA-MATNR,
END OF TY_MCHA.

TYPES : BEGIN OF TY_MBEW,
LBKUM LIKE MBEW-LBKUM,
SALK3 LIKE MBEW-SALK3,
MATNR LIKE MBEW-MATNR,
END OF TY_MBEW.

DATA : T_RESULT TYPE TABLE OF TY_RESULT,
GS_RESULT TYPE TY_RESULT,
T_MCHA TYPE TABLE OF TY_MCHA,
GS_MCHA TYPE TY_MCHA,
T_MCHB TYPE TABLE OF TY_MCHB,
GS_MCHB TYPE TY_MCHB,
T_MAKT TYPE TABLE OF TY_MAKT,
GS_MAKT TYPE TY_MAKT,
T_T001W TYPE TABLE OF TY_T001W,
GS_T001W TYPE TY_T001W,
T_MARA TYPE TABLE OF TY_MARA,
GS_MARA TYPE TY_MARA,
T_MSEG TYPE TABLE OF TY_MSEG,
GS_MSEG TYPE TY_MSEG,
T_MBEW TYPE TABLE OF TY_MBEW,
GS_MBEW TYPE TY_MBEW,
T_MCT0 TYPE TABLE OF TY_MCT0,
GS_MCT0 TYPE TY_MCT0.
DATA : TYPE_DATE LIKE SY-DATUM.
DATA : T_FCAT TYPE SLIS_T_FIELDCAT_ALV,
GS_FCAT LIKE LINE OF T_FCAT,
GS_LAYO TYPE SLIS_LAYOUT_ALV.

INITIALIZATION.
SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-B01.
SELECT-OPTIONS: S_MATNR FOR MARA-MATNR, "MATERIAL
S_WERKS FOR MCHB-WERKS, "PLANT
S_LGORT FOR MCHB-LGORT. "SLOC
SELECTION-SCREEN END OF BLOCK B1.
START-OF-SELECTION.

IF S_MATNR[] IS NOT INITIAL.

REFRESH : T_MARA.
CLEAR : GS_MARA.

SELECT BISMT
MTART
MATNR
FROM MARA
INTO TABLE T_MARA
WHERE MATNR IN S_MATNR
AND MTART NOT IN ('ZNS','ZNV','ZSR').

ENDIF.

IF T_MARA[] IS NOT INITIAL.

SELECT MAKTX
MATNR
FROM MAKT
INTO TABLE T_MAKT
FOR ALL ENTRIES IN T_MARA
WHERE MATNR = T_MARA-MATNR.

SELECT LBKUM
SALK3
MATNR
FROM MBEW
INTO TABLE T_MBEW
FOR ALL ENTRIES IN T_MARA
WHERE MATNR = T_MARA-MATNR.

SELECT VFDAT
MATNR
FROM MCHA
INTO TABLE T_MCHA
FOR ALL ENTRIES IN T_MARA
WHERE MATNR = T_MARA-MATNR.

ENDIF.

IF T_MAKT[] IS NOT INITIAL.

SELECT A~WERKS
A~MATNR
A~LGORT
SUM( A~CLABS ) AS SUM_CLABS
A~CHARG
B~NAME1
INTO TABLE T_MCT0
FROM MCHB AS A
INNER JOIN T001W AS B
ON B~WERKS = A~WERKS
WHERE A~WERKS IN S_WERKS
AND A~LGORT IN S_LGORT
AND A~LGORT = 'WR02'
AND A~WERKS IN ('2297','21B7')
GROUP BY A~WERKS A~MATNR A~LGORT A~CHARG B~NAME1.

ENDIF.

SELECT MAX( BUDAT_MKPF ) AS MAX_BUDAT_MKPF
MATNR
WERKS
BWART
FROM MSEG
INTO TABLE T_MSEG
WHERE WERKS IN S_WERKS
AND MATNR IN S_MATNR
AND BWART = '101'
GROUP BY MATNR WERKS BWART.

LOOP AT T_MARA INTO GS_MARA.
GS_RESULT-MATNR = GS_MARA-MATNR.
GS_RESULT-BISMT = GS_MARA-BISMT.

READ TABLE T_MCT0 INTO GS_MCT0 WITH KEY MATNR = GS_MARA-MATNR.
GS_RESULT-WERKS = GS_MCT0-WERKS.
GS_RESULT-LGORT = GS_MCT0-LGORT.
GS_RESULT-SUM_CLABS = GS_MCT0-CLABS.
GS_RESULT-CHARG = GS_MCT0-CHARG.
GS_RESULT-NAME1 = GS_MCT0-NAME1.
IF S_MATNR = 'X'.
IF S_WERKS = 'X'.
IF S_LGORT = 'X'.
ENDIF.
ENDIF.
ENDIF.

READ TABLE T_MBEW INTO GS_MBEW WITH KEY MATNR = GS_MARA-MATNR.
GS_RESULT-SALK3 = GS_MBEW-SALK3.
IF GS_MBEW-LBKUM = '0'.
GS_RESULT-SUM_LBKUM = GS_MBEW-LBKUM.
ELSEIF GS_MBEW-LBKUM <> '0'.
GS_RESULT-SUM_LBKUM = ( GS_RESULT-SALK3 * 100 ) / GS_MBEW-LBKUM. "GET AVGCOST
ENDIF.

* GS_RESULT-STOCVAL = GS_RESULT-SUM_CLABS * GS_RESULT-SUM_LBKUM. "GET STOCK VALUE

READ TABLE T_MSEG INTO GS_MSEG WITH KEY MATNR = GS_MARA-MATNR.
S_WERKS = GS_MCHB-WERKS.
IF SY-SUBRC EQ 0.
GS_RESULT-MAX_BUDAT_MKPF = GS_RESULT-MAX_BUDAT_MKPF.
ENDIF.

* GS_RESULT-AGING = GS_RESULT-MAX_BUDAT_MKPF

READ TABLE T_MAKT INTO GS_MAKT WITH KEY MATNR = GS_MARA-MATNR.
IF SY-SUBRC EQ 0.
GS_RESULT-MAKTX = GS_MAKT-MAKTX.
ENDIF.

READ TABLE T_T001W INTO GS_T001W WITH KEY WERKS = GS_MCHB-WERKS.
IF SY-SUBRC EQ 0.
GS_RESULT-NAME1 = GS_T001W-NAME1.
ENDIF.

READ TABLE T_MCHA INTO GS_MCHA WITH KEY MATNR = GS_MARA-MATNR.
IF SY-SUBRC EQ 0.
GS_RESULT-VFDAT = GS_MCHA-VFDAT.
ENDIF.

APPEND GS_RESULT TO T_RESULT.
CLEAR GS_RESULT.

ENDLOOP.

CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'WERKS'.
GS_FCAT-COL_POS = 1.
GS_FCAT-SELTEXT_M = 'PLANT'.
APPEND GS_FCAT TO T_FCAT.

CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'NAME1'.
GS_FCAT-COL_POS = 2.
GS_FCAT-SELTEXT_M = 'PLANT NAME'.
APPEND GS_FCAT TO T_FCAT.

CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'MATNR'.
GS_FCAT-COL_POS = 3.
GS_FCAT-SELTEXT_M = 'MATERIAL'.
APPEND GS_FCAT TO T_FCAT.

CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'BISMT'.
GS_FCAT-COL_POS = 4.
GS_FCAT-SELTEXT_M = 'SKU'.
APPEND GS_FCAT TO T_FCAT.

CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'MAKTX'.
GS_FCAT-COL_POS = 5.
GS_FCAT-SELTEXT_M = 'DESCRIPTIONS'.
APPEND GS_FCAT TO T_FCAT.

CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'LGORT'.
GS_FCAT-COL_POS = 6.
GS_FCAT-SELTEXT_M = 'SLOC'.
APPEND GS_FCAT TO T_FCAT.

CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'SUM_CLABS'.
GS_FCAT-COL_POS = 7.
GS_FCAT-SELTEXT_M = 'STOCK QTY'.
APPEND GS_FCAT TO T_FCAT.

CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'SUM_LBKUM'.
GS_FCAT-COL_POS = 8.
GS_FCAT-SELTEXT_M = 'AVG COST'.
APPEND GS_FCAT TO T_FCAT.

CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'STOCVAL'.
GS_FCAT-COL_POS = 9.
GS_FCAT-SELTEXT_M = 'STOCK VALUE'.
APPEND GS_FCAT TO T_FCAT.

CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'LASTRECV'.
GS_FCAT-COL_POS = 10.
GS_FCAT-SELTEXT_M = 'LAST RECV'.
APPEND GS_FCAT TO T_FCAT.

CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'AGING'.
GS_FCAT-COL_POS = 11.
GS_FCAT-SELTEXT_M = 'AGING'.
APPEND GS_FCAT TO T_FCAT.

CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'CHARG'.
GS_FCAT-COL_POS = 12.
GS_FCAT-SELTEXT_M = 'BATCH'.
APPEND GS_FCAT TO T_FCAT.

CLEAR GS_FCAT.
GS_FCAT-TABNAME = 'T_RESULT'.
GS_FCAT-FIELDNAME = 'VFDAT'.
GS_FCAT-COL_POS = 13.
GS_FCAT-SELTEXT_M = 'BATCH'.
APPEND GS_FCAT TO T_FCAT.

GS_LAYO-ZEBRA = 'X'.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
I_CALLBACK_PROGRAM = SY-REPID
I_CALLBACK_USER_COMMAND = 'USER_COMMAND'
I_GRID_TITLE = 'REPORT AGING STOCK' "BUAT JUDUL DI ATAS OUTPUT REPORT
IS_LAYOUT = GS_LAYO
IT_FIELDCAT = T_FCAT
TABLES
T_OUTTAB = T_RESULT. "MASUKIN INTERNAL TABEL PENAMPUNF SEMUA OUTPUT YANG AKAN DITAMPILKAN

IF SY-SUBRC <> 0.
ENDIF.

FORM USER_COMMAND USING RS_COMM TYPE SY-UCOMM
RS_SEL TYPE SLIS_SELFIELD.
CASE RS_COMM.
WHEN '&IC1'.
READ TABLE T_RESULT
INTO GS_RESULT
INDEX RS_SEL-TABINDEX.
ENDCASE.
ENDFORM.