存货库龄分析表代码

*&---------------------------------------------------------------------*
*& Report ZMMR02
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT zmmr02 NO STANDARD PAGE HEADING.
TABLES: mchb,mseg,makt,mara,MBEW.

TYPE-POOLS: ole2.

CONSTANTS: c_sheet(4) TYPE c VALUE 'Real'.

DATA: filetitle(100) TYPE c.

*DATA: IT_MSEG LIKE TABLE OF MSEG WITH HEADER LINE.
DATA: BEGIN OF IT_MSEG OCCURS 0,
MATNR LIKE MSEG-MATNR,
WERKS LIKE MSEG-WERKS,
LGORT LIKE MSEG-LGORT,
CHARG LIKE MSEG-CHARG,
SHKZG LIKE MSEG-SHKZG,
WAERS LIKE MSEG-WAERS,
DMBTR LIKE MSEG-DMBTR,
MENGE LIKE MSEG-MENGE,
MEINS LIKE MSEG-MEINS,
MAKTX LIKE MAKT-MAKTX,
LBKUM LIKE MBEW-LBKUM,
SALK3 LIKE MBEW-SALK3,
STPRS LIKE MBEW-STPRS,
PEINH LIKE MBEW-PEINH,
END OF IT_MSEG.
DATA: IT_MSEG1 LIKE TABLE OF MSEG WITH HEADER LINE.
*DATA: IT_MSEG2 LIKE STANDARD TABLE OF MSEG WITH HEADER LINE.
DATA :BEGIN OF IT_MSEG3 OCCURS 0,
MATNR LIKE MSEG-MATNR,
MENGE LIKE MSEG-MENGE,
* DMBTR LIKE MSEG-DMBTR,
LBKUM LIKE MBEW-LBKUM,
SALK3 LIKE MBEW-SALK3,
MAKTX LIKE MAKT-MAKTX,
MEINS LIKE MARA-MEINS,
END OF IT_MSEG3.
DATA: IT_MSEG2 LIKE TABLE OF IT_MSEG WITH HEADER LINE.
DATA: IT_MSEG4 LIKE TABLE OF IT_MSEG3 WITH HEADER LINE.
DATA: IT_MSEG7 LIKE TABLE OF MSEG WITH HEADER LINE.
DATA:BEGIN OF IT_MCHB3 OCCURS 0,
matnr LIKE mchb-matnr,
werks LIKE mchb-werks,
charg LIKE mchb-charg,
* ersda LIKE mchb-ersda, "物料对应批次在系统中的创建时间
CLABS LIKE mchb-CLABS,
CUMLM LIKE mchb-CUMLM,
CINSM LIKE mchb-CINSM,
CEINM LIKE mchb-CEINM,
CSPEM LIKE mchb-CSPEM,
CRETM LIKE mchb-CRETM,
CVMLA LIKE mchb-CVMLA,
CVMUM LIKE mchb-CVMUM,
CVMIN LIKE mchb-CVMIN,
CVMEI LIKE mchb-CVMEI,
CVMSP LIKE mchb-CVMSP,
CVMRE LIKE mchb-CVMRE,
MEINS LIKE mara-meins,
MAKTX LIKE MAKT-MAKTX,
LPLPR LIKE MBEW-LPLPR,
END OF IT_MCHB3.

"该内表用于处理供应商库存的状况：总数量为0，而库存总价值不为0
DATA: BEGIN OF IT_MCHB4 OCCURS 0,
MATNR LIKE MSEG-MATNR,
CHARG LIKE MSEG-CHARG,
MENGE LIKE MSEG-MENGE,
* DMBTR LIKE MSEG-DMBTR,
END OF IT_MCHB4.
DATA: IT_MCHB5 LIKE TABLE OF IT_MCHB4 WITH HEADER LINE.
DATA :BEGIN OF IT_MCHB6 OCCURS 0,
MATNR LIKE MSEG-MATNR,
CHARG LIKE MSEG-CHARG,
MENGE LIKE MSEG-MENGE,
* DMBTR LIKE MSEG-DMBTR,
STOCKTIME TYPE MCHB-ERSDA, "根据批次号算出其对应的入库时间
DAY TYPE MCHB-ERSDA, "算出某一批次距离查询时间点的时间长度
MARK TYPE C,
END OF IT_MCHB6.

DATA:BEGIN OF IT_MCHB OCCURS 0,
matnr LIKE mard-matnr,
werks LIKE mard-werks,
charg LIKE mchb-charg,
TOTALQTY LIKE MSEG-MENGE,
DMBTR LIKE MSEG-DMBTR,
* ersda LIK

E mchb-ersda, "物料对应批次在系统中的创建时间
* CLABS LIKE mchb-CLABS,
* CUMLM LIKE mchb-CUMLM,
* CINSM LIKE mchb-CINSM,
* CEINM LIKE mchb-CEINM,
* CSPEM LIKE mchb-CSPEM,
* CRETM LIKE mchb-CRETM,
* CVMLA LIKE mchb-CVMLA,
* CVMUM LIKE mchb-CVMUM,
* CVMIN LIKE mchb-CVMIN,
* CVMEI LIKE mchb-CVMEI,
* CVMSP LIKE mchb-CVMSP,
* CVMRE LIKE mchb-CVMRE,
MEINS LIKE mara-meins,
MAKTX LIKE MAKT-MAKTX,

END OF IT_MCHB.
DATA IT_MCHB1 LIKE TABLE OF IT_MCHB WITH HEADER LINE.

"it_mchb2中，将会算出某一批次距离查询时间点的时长
DATA: BEGIN OF it_mchb2 OCCURS 0,
matnr LIKE mard-matnr,
* matkl LIKE mara-matkl,
werks LIKE mard-werks,
* lgort LIKE mchb-lgort,
charg LIKE mchb-charg,
ersda LIKE mchb-ersda, "物料对应批次在系统中的创建时间
CLABS LIKE mchb-CLABS,
CUMLM LIKE mchb-CUMLM,
CINSM LIKE mchb-CINSM,
CEINM LIKE mchb-CEINM,
CSPEM LIKE mchb-CSPEM,
CRETM LIKE mchb-CRETM,
CVMLA LIKE mchb-CVMLA,
CVMUM LIKE mchb-CVMUM,
CVMIN LIKE mchb-CVMIN,
CVMEI LIKE mchb-CVMEI,
CVMSP LIKE mchb-CVMSP,
CVMRE LIKE mchb-CVMRE,
STOCKTIME TYPE MCHB-ERSDA, "根据批次号算出其对应的入库时间
DAY TYPE MCHB-ERSDA, "算出某一批次距离查询时间点的时间长度
* S_TIME1 TYPE MCHB-ERSDA,
MARK TYPE C, "做的Mark
DMBTR LIKE MSEG-DMBTR,
TOTALQTY LIKE MSEG-MENGE,
MEINS LIKE mara-meins,
MAKTX LIKE MAKT-MAKTX,

END OF it_mchb2.


DATA: BEGIN OF it_tab OCCURS 0,
matnr LIKE mchb-matnr,
* icolum TYPE C,
* menge like MSEG-MENGE,
* lgort LIKE mchb-lgort,
* charg LIKE mchb-charg,
menge1 like mseg-menge,
netwr1 like mseg-DMBTR,
menge2 like mseg-menge,
netwr2 like mseg-DMBTR,
menge3 like mseg-menge,
netwr3 like mseg-DMBTR,
menge4 like mseg-menge,
netwr4 like mseg-DMBTR,
menge5 like mseg-menge,
netwr5 like mseg-DMBTR,
menge6 like mseg-menge,
netwr6 like mseg-DMBTR,
menge7 like mseg-menge,
netwr7 like mseg-DMBTR,
menge8 like mseg-menge,
netwr8 like mseg-DMBTR,
MAKTX LIKE MAKT-MAKTX,
meins LIKE mara-meins,
LBKUM LIKE MBEW-LBKUM, "库存总量
SALK3 LIKE MBEW-SALK3, "库存总价值
icolum TYPE c,
ICOLUM1 TYPE C,
END OF it_tab.

*DATA: IT_TAB1 LIKE STANDARD TABLE OF IT_TAB WITH HEADER LINE.
DATA: BEGIN OF it_tab1 OCCURS 0,
matnr LIKE mchb-matnr,
* icolum TYPE C,
* menge like MSEG-MENGE,
* lgort LIKE mchb-lgort,
* charg LIKE mchb-charg,
menge1 like mseg-menge,
netwr1 lik

e mseg-DMBTR,
menge2 like mseg-menge,
netwr2 like mseg-DMBTR,
menge3 like mseg-menge,
netwr3 like mseg-DMBTR,
menge4 like mseg-menge,
netwr4 like mseg-DMBTR,
menge5 like mseg-menge,
netwr5 like mseg-DMBTR,
menge6 like mseg-menge,
netwr6 like mseg-DMBTR,
menge7 like mseg-menge,
netwr7 like mseg-DMBTR,
menge8 like mseg-menge,
netwr8 like mseg-DMBTR,
MAKTX LIKE MAKT-MAKTX,
meins LIKE mara-meins,
LBKUM LIKE MBEW-LBKUM, "库存总量
SALK3 LIKE MBEW-SALK3, "库存总价值
icolum TYPE C,
ICOLUM1 TYPE C,
END OF it_tab1.

DATA:IT_TAB2 LIKE TABLE OF IT_TAB1 WITH HEADER LINE.


DATA: it_fieldcat TYPE lvc_t_fcat,
is_fieldcat LIKE LINE OF it_fieldcat.
DATA: w_excel TYPE ole2_object, " Excel object,ole_record.
w_sheet TYPE ole2_object, " Excel object,worksheets
w_mapl TYPE ole2_object, " list of workbooks
w_map TYPE ole2_object, " workbook
w_rangeobj TYPE ole2_object, " Range object
w_zl TYPE ole2_object, " cell
w_mapl01 TYPE ole2_object,
w_f TYPE ole2_object. " font
RANGES: r_matnr FOR mara-matnr.
DATA: w_temp_file(65), "Temp file path for excel template file.
w_formkey LIKE wwwdatatab VALUE 'MIZMMR02',
w_ftemplate LIKE rlgrap-filename VALUE 'C:\WINDOWS\Temp\ZMMR02.XLT',
w_fdata LIKE rlgrap-filename VALUE 'C:\WINDOWS\temp\ZMMR02.TXT'.

DATA: BEGIN OF i_data OCCURS 0,
sheet(4) TYPE c,
row(4) TYPE c,
column(4) TYPE c,
value(50) TYPE c,
END OF i_data.

SELECTION-SCREEN BEGIN OF BLOCK b0 WITH FRAME TITLE text-001.

SELECT-OPTIONS: s_matnr FOR MSEG-matnr,
s_werks FOR MSEG-werks,
s_lgort FOR MSEG-lgort,
s_charg FOR MSEG-charg.

SELECTION-SCREEN END OF BLOCK b0.

*INITIALIZATION.
START-OF-SELECTION.
PERFORM FRM_BUILD_DATA.
PERFORM frm_to_excel.

END-OF-SELECTION.

"将所有物料的出入库信息放到it_mseg中
*&---------------------------------------------------------------------*
*& Form FRM_BUILD_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM FRM_BUILD_DATA.
SELECT * FROM MSEG
* INNER JOIN MSEG ON ( MCHB~MATNR = MSEG~MATNR )
* INNER JOIN MAKT ON ( MAKT~MATNR = MARA~MATNR )
INNER JOIN MAKT ON ( MAKT~MATNR = MSEG~MATNR )
INNER JOIN MBEW ON ( MSEG~MATNR = MBEW~MATNR )
* INNER JOIN MARD ON ( MARD~MATNR = MSEG~MATNR )
INTO CORRESPONDING FIELDS OF TABLE IT_MSEG
WHERE MSEG~MATNR IN s_matnr
AND MSEG~WERKS in s_werks
AND MSEG~lgort IN s_lgort
AND MSEG~charg IN s_charg.

LOOP AT IT_MSEG.
cal

l function 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
input = IT_MSEG-MATNR
IMPORTING
output = IT_MSEG-MATNR.
MODIFY IT_MSEG.
ENDLOOP.



"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
"以下代码用于处理没有批次的物料，此时仅输出总库存与总价值"
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
LOOP AT IT_MSEG WHERE CHARG = ''.
MOVE-CORRESPONDING IT_MSEG TO IT_MSEG2.
APPEND IT_MSEG2.
ENDLOOP.

LOOP AT IT_MSEG2.
* IF IT_MSEG2-SHKZG = ''.
IT_MSEG3-MATNR = IT_MSEG2-MATNR.
IT_MSEG3-MENGE = IT_MSEG2-MENGE.
APPEND IT_MSEG3.
ENDLOOP.

LOOP AT IT_MSEG3.
COLLECT IT_MSEG3 INTO IT_MSEG4.
ENDLOOP.

LOOP AT IT_MSEG4 .
* SELECT SINGLE LBKUM SALK3 INTO (IT_MSEG4-LBKUM , IT_MSEG4-SALK3) FROM MSEG
* WHERE MATNR = IT_MSEG4-MATNR.
* SELECT SINGLE MAKTX FROM MAKT INTO IT_MSEG4-MAKTX WHERE
* MATNR = IT_MSEG4-MATNR.
* SELECT SINGLE MEINS FROM MSEG INTO IT_MSEG4-MEINS WHERE
* MATNR = IT_MSEG4-MATNR.
READ TABLE IT_MSEG WITH KEY MATNR = IT_MSEG4-MATNR.
IF SY-SUBRC = 0.
IT_MSEG4-LBKUM = IT_MSEG-LBKUM.
IT_MSEG4-SALK3 = IT_MSEG-SALK3.
IT_MSEG4-MAKTX = IT_MSEG-MAKTX.
IT_MSEG4-MEINS = IT_MSEG-MEINS .
MODIFY IT_MSEG4.
ENDIF.

ENDLOOP.
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'''''
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""



DELETE IT_MSEG WHERE CHARG = ''.

""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'""""
" 以下代码用于处理有批次号的物料
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'
LOOP AT IT_MSEG WHERE CHARG NE ''.
MOVE-CORRESPONDING IT_MSEG TO IT_MSEG7.
* SELECT DISTINCT * FROM MCHB INTO CORRESPONDING FIELDS OF TABLE IT_MCHB3
* WHERE MATNR = IT_MSEG
APPEND IT_MSEG7.
ENDLOOP.


*LOOP AT IT_MCHB3.
* call function 'CONVERSION_EXIT_ALPHA_OUTPUT'
* EXPORTING
* input = IT_MCHB3-MATNR
* IMPORTING
* output = IT_MCHB3-MATNR.
*MODIFY IT_MCHB3.
*
* ENDLOOP.

LOOP AT IT_MSEG7.
IF IT_MSEG7-SHKZG = 'H'.
IT_MSEG7-MENGE = 0 - IT_MSEG7-MENGE.
* IT_MSEG7-DMBTR = 0 - IT_MSEG7-DMBTR.
MODIFY IT_MSEG7.
ENDIF.
ENDLOOP.
LOOP AT IT_MSEG7.
IT_MCHB-matnr = IT_MSEG7-matnr.
IT_MCHB-werks = IT_MSEG7-werks.
IT_MCHB-charg = IT_MSEG7-charg.
IT_MCHB-TOTALQTY = IT_MSEG7-MENGE.
* IT_MCHB-TOTALQTY = IT_MSEG7-MENGE.
* IT_MCHB-DMBTR = IT_MSEG7-DMBTR.
* IT_MCHB-CLABS = IT_MCHB3-CLABS.
* IT_MCHB-CUMLM = IT_MCHB3-CUMLM.
* IT_MCHB-CINSM = IT_MCHB3-CINSM.
* IT_MCHB-CEINM = IT_MCHB3-CEINM.
* IT_MCHB-CSPEM = IT_MCHB3-CSPEM.
* IT_MCHB-CRETM = IT_MCHB3-CRETM.
* IT_MCHB-CVMLA = IT_MCHB3-CVMLA.
* IT_MCHB-CVMUM = IT_MCHB3-CVMUM.
* IT_MCHB-CVMIN = IT_MCHB3-CVMIN.
* IT_MCHB-CVMEI = IT_MCHB3-CVMEI.
* IT_MCHB-CVMSP = IT

_MCHB3-CVMSP.
* IT_MCHB-CVMRE = IT_MCHB3-CVMRE.
* IT_MCHB-MAKTX = IT_MCHB3-MAKTX.
* IT_MCHB-MEINS = IT_MCHB3-MEINS.
APPEND IT_MCHB.
ENDLOOP.

LOOP AT IT_MCHB.
COLLECT IT_MCHB INTO IT_MCHB1.
ENDLOOP.

LOOP AT IT_MCHB1.
MOVE-CORRESPONDING IT_MCHB1 TO IT_MCHB2.
APPEND IT_MCHB2.
ENDLOOP.

""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'
"以下代码完成下列功能：
"在it_mchb2中计算出下列值：距离查询时间点的长度、该长度对应的MARK
"该批次对应的货物总值 以及 同一批次的总量等信息
*DATA S_TIME1 TYPE MCHB-ERSDA.

"计算出距离查询时间点的长度，并将不在查询时间点内的删除掉
LOOP AT IT_MCHB2.
CONCATENATE '20' IT_MCHB2-charg+1(6) INTO IT_MCHB2-STOCKTIME.
IF SY-DATUM < IT_MCHB2-STOCKTIME.
IT_MCHB2-DAY = 0.
ELSE.
IT_MCHB2-DAY = SY-DATUM - IT_MCHB2-STOCKTIME.
ENDIF.
MODIFY IT_MCHB2.
ENDLOOP.
*DELETE IT_MCHB2 WHERE DAY = 0.

"计算同一批次的总数量\并根据时长作上相应的MARK
LOOP AT IT_MCHB2.
* IT_MCHB2-TOTALQTY = IT_MCHB2-CLABS + IT_MCHB2-CUMLM + IT_MCHB2-CINSM +
* IT_MCHB2-CEINM + IT_MCHB2-CSPEM + IT_MCHB2-CRETM + IT_MCHB2-CVMLA +
* IT_MCHB2-CVMUM + IT_MCHB2-CVMIN + IT_MCHB2-CVMEI + IT_MCHB2-CVMSP + IT_MCHB2-CVMRE.
* IT_MCHB2-TOTALQTY = IT_MCHB2-MENGE.
IF 1 <= IT_MCHB2-DAY AND IT_MCHB2-DAY <= 7.
IT_MCHB2-MARK = 'A'.
ELSEIF 8 <= IT_MCHB2-DAY AND IT_MCHB2-DAY <= 14.
IT_MCHB2-MARK = 'B'.
ELSEIF 15 <= IT_MCHB2-DAY AND IT_MCHB2-DAY <= 21.
IT_MCHB2-MARK = 'C'.
ELSEIF 22 <= IT_MCHB2-DAY AND IT_MCHB2-DAY <= 30.
IT_MCHB2-MARK = 'D'.
ELSEIF 31 <= IT_MCHB2-DAY AND IT_MCHB2-DAY <= 60.
IT_MCHB2-MARK = 'E'.
ELSEIF 61 <= IT_MCHB2-DAY AND IT_MCHB2-DAY <= 90.
IT_MCHB2-MARK = 'F'.
ELSEIF 91 <= IT_MCHB2-DAY AND IT_MCHB2-DAY <= 120.
IT_MCHB2-MARK = 'G'.
ELSEIF 121 <= IT_MCHB2-DAY .
IT_MCHB2-MARK = 'H'.
ENDIF.
MODIFY IT_MCHB2.
ENDLOOP.

"以下代码用于将具有同一批次的物料的总价值算出
"
*LOOP AT IT_MSEG.
* IF IT_MSEG-SHKZG = 'H'.
* IT_MSEG-DMBTR = 0 - IT_MSEG-DMBTR.
* IT_MSEG-MENGE = 0 - IT_MSEG-MENGE.
* MODIFY IT_MSEG.
* ENDIF.
*ENDLOOP.
*LOOP AT IT_MSEG.
* IT_MSEG3-MATNR = IT_MSEG-MATNR.
* IT_MSEG3-CHARG = IT_MSEG-CHARG.
* IT_MSEG3-DMBTR = IT_MSEG-DMBTR.
* APPEND IT_MSEG3.
*ENDLOOP.
*LOOP AT IT_MSEG3.
* COLLECT IT_MSEG3 INTO IT_MSEG4.
*ENDLOOP.
*
*
**将IT_MSEG4中具有同一物料号+批次的货物总价值赋给IT_MCHB1中的DMBTR字段
*LOOP AT IT_MCHB2.
* READ TABLE IT_MSEG4 WITH KEY MATNR = IT_MCHB2-MATNR charg = IT_MCHB2-charg.
* IF SY-SUBRC = 0.
* IT_MCHB2-DMBTR = IT_MSEG4-DMBTR.
* ENDIF.
* MODIFY IT

_MCHB2.
*ENDLOOP.

""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""






LOOP AT IT_MCHB2.
IT_TAB-MATNR = IT_MCHB2-MATNR.
IT_TAB-MAKTX = IT_MCHB2-MAKTX.
IT_TAB-meins = IT_MCHB2-meins.
* IT_TAB-DMBTR = IT_MCHB2-DMBTR.
CASE IT_MCHB2-MARK.
WHEN 'A'.
IT_TAB-menge1 = IT_MCHB2-TOTALQTY.
* IT_TAB-netwr1 = IT_MCHB2-DMBTR.
* IT_TAB-netwr1 = IT_TAB-menge1 * .
WHEN 'B'.
IT_TAB-menge2 = IT_MCHB2-TOTALQTY.
* IT_TAB-netwr2 = IT_MCHB2-DMBTR.

WHEN 'C'.
IT_TAB-menge3 = IT_MCHB2-TOTALQTY.
* IT_TAB-netwr3 = IT_MCHB2-DMBTR.
WHEN 'D'.
IT_TAB-menge4 = IT_MCHB2-TOTALQTY.
* IT_TAB-netwr4 = IT_MCHB2-DMBTR.
WHEN 'E'.
IT_TAB-menge5 = IT_MCHB2-TOTALQTY.
* IT_TAB-netwr5 = IT_MCHB2-DMBTR.
WHEN 'F'.
IT_TAB-menge6 = IT_MCHB2-TOTALQTY.
* IT_TAB-netwr6 = IT_MCHB2-DMBTR.
WHEN 'G'.
IT_TAB-menge7 = IT_MCHB2-TOTALQTY.
* IT_TAB-netwr7 = IT_MCHB2-DMBTR.
WHEN 'H'.
IT_TAB-menge8 = IT_MCHB2-TOTALQTY.
* IT_TAB-netwr8 = IT_MCHB2-DMBTR.
ENDCASE.
* SELECT SINGLE MAKTX INTO IT_TAB1-MAKTX FROM MAKT WHERE
* MATNR = IT_TAB1-MATNR.
* SELECT SINGLE MEINS INTO IT_TAB1-MEINS FROM MARA WHERE
* MATNR = IT_TAB1-MATNR.

APPEND IT_TAB.
CLEAR IT_TAB.
ENDLOOP.
*
LOOP AT IT_TAB.
COLLECT IT_TAB INTO IT_TAB1.
ENDLOOP.

LOOP AT IT_TAB1.

READ TABLE IT_MSEG WITH KEY MATNR = IT_TAB1-MATNR.
IF SY-SUBRC = 0.
IT_TAB1-NETWR1 = ( IT_TAB1-MENGE1 * IT_MSEG-STPRS ) / IT_MSEG-PEINH.
IT_TAB1-NETWR2 = ( IT_TAB1-MENGE2 * IT_MSEG-STPRS ) / IT_MSEG-PEINH.
IT_TAB1-NETWR3 = ( IT_TAB1-MENGE3 * IT_MSEG-STPRS ) / IT_MSEG-PEINH.
IT_TAB1-NETWR4 = ( IT_TAB1-MENGE4 * IT_MSEG-STPRS ) / IT_MSEG-PEINH.
IT_TAB1-NETWR5 = ( IT_TAB1-MENGE5 * IT_MSEG-STPRS ) / IT_MSEG-PEINH.
IT_TAB1-NETWR6 = ( IT_TAB1-MENGE6 * IT_MSEG-STPRS ) / IT_MSEG-PEINH.
IT_TAB1-NETWR7 = ( IT_TAB1-MENGE7 * IT_MSEG-STPRS ) / IT_MSEG-PEINH.
IT_TAB1-NETWR8 = ( IT_TAB1-MENGE8 * IT_MSEG-STPRS ) / IT_MSEG-PEINH.
IT_TAB1-MAKTX = IT_MSEG-MAKTX.
IT_TAB1-MEINS = IT_MSEG-MEINS.
MODIFY IT_TAB1.
ENDIF.
* CLEAR IT_MCHB3.
* CONTINUE.
ENDLOOP.

*LOOP AT IT_TAB1.
** SELECT SINGLE MAKTX INTO IT_TAB1-MAKTX FROM MAKT WHERE
** MATNR = IT_TAB1-MATNR.
** SELECT SINGLE MEINS INTO IT_TAB1-MEINS FROM MARA WHERE
** MATNR = IT_TAB1-MATNR.
*
*READ TABLE IT_MSEG WITH KEY MATNR = IT_MSEG-MATNR.
*IF SY-SUBRC = 0.
* IT_TAB1-MAKTX = IT_MSEG-MAKTX.
* IT_TAB1-MEINS = IT_MSEG-MEINS.
* ENDIF.
* MODIFY IT_TAB1.
* CLEAR IT_MSEG.
*
*ENDLOOP.

LOOP AT IT_TAB1.
IT_TAB1-LBKUM = IT_TAB1-menge1 + IT_TAB1-menge2 + IT_TAB1-menge3
+ IT_TAB1-menge4 + IT_TAB1-menge5 + IT_TAB1-menge6 + IT_TAB1-menge7
+ IT_TAB1-menge8

.
IT_TAB1-SALK3 = IT_TAB1-netwr1 + IT_TAB1-netwr2 + IT_TAB1-netwr3
+ IT_TAB1-netwr4 + IT_TAB1-netwr5 + IT_TAB1-netwr6 + IT_TAB1-netwr7
+ IT_TAB1-netwr8.
MODIFY IT_TAB1.
* CLEAR IT_MSEG.
ENDLOOP.

IF IT_MSEG4 IS NOT INITIAL.
CLEAR IT_TAB1.
LOOP AT IT_MSEG4.
IT_TAB1-MATNR = IT_MSEG4-MATNR.
IT_TAB1-maktx = IT_MSEG4-MAKTX.
IT_TAB1-MEINS = IT_MSEG4-MEINS.
IT_TAB1-LBKUM = IT_MSEG4-LBKUM.
IT_TAB1-SALK3 = IT_MSEG4-SALK3.
APPEND IT_TAB1.
ENDLOOP.
ENDIF.

SORT IT_TAB1 BY MATNR.
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'
"以下代码用于处理存货中的供应商库存 状况;存货的库存为0，而货物价值存在
"因为货物价值是从MSEG表中取出
*LOOP AT IT_TAB1.
* IF IT_TAB1-LBKUM = 0 AND IT_TAB1-SALK3 NE 0.
* IT_TAB1-ICOLUM1 = 'X'.
** MOVE-CORRESPONDING IT_TAB TO IT_TAB2.
* IT_TAB2-MATNR = IT_TAB1-MATNR.
* APPEND IT_TAB2.
* MODIFY IT_TAB1.
*
* ENDIF.
** CLEAR IT_TAB1.
* ENDLOOP.
*LOOP AT IT_TAB2 .
* LOOP AT IT_MSEG.
* IF IT_MSEG-MATNR = IT_TAB2-MATNR.
** READ TABLE IT_MSEG WITH KEY MATNR = IT_TAB2-MATNR.
** IF SY-SUBRC = 0.
* IT_MCHB4-MATNR = IT_MSEG-MATNR.
* IT_MCHB4-CHARG = IT_MSEG-CHARG.
* IT_MCHB4-MENGE = IT_MSEG-MENGE.
* APPEND IT_MCHB4.
** ENDIF.
* ENDIF.
* ENDLOOP.
* ENDLOOP.
*
* LOOP AT IT_MCHB4.
* COLLECT IT_MCHB4 INTO IT_MCHB5.
* ENDLOOP.
*
* LOOP AT IT_MCHB5.
* IT_MCHB6-MATNR = IT_MCHB5-MATNR.
* IT_MCHB6-CHARG = IT_MCHB5-CHARG.
* IT_MCHB6-MENGE = IT_MCHB5-MENGE.
** IT_MCHB6-DMBTR = IT_MCHB5-DMBTR.
* APPEND IT_MCHB6.
* ENDLOOP.
*
* LOOP AT IT_MCHB6.
* CONCATENATE '20' IT_MCHB6-charg+1(6) INTO IT_MCHB6-STOCKTIME.
* IF SY-DATUM < IT_MCHB6-STOCKTIME.
* IT_MCHB2-DAY = 0.
* ELSE.
* IT_MCHB6-DAY = SY-DATUM - IT_MCHB6-STOCKTIME.
* ENDIF.
* IF 1 <= IT_MCHB6-DAY AND IT_MCHB6-DAY <= 7.
* IT_MCHB6-MARK = 'A'.
* ELSEIF 8 <= IT_MCHB6-DAY AND IT_MCHB6-DAY <= 14.
* IT_MCHB6-MARK = 'B'.
* ELSEIF 15 <= IT_MCHB6-DAY AND IT_MCHB6-DAY <= 21.
* IT_MCHB6-MARK = 'C'.
* ELSEIF 22 <= IT_MCHB6-DAY AND IT_MCHB6-DAY <= 30.
* IT_MCHB6-MARK = 'D'.
* ELSEIF 31 <= IT_MCHB6-DAY AND IT_MCHB6-DAY <= 60.
* IT_MCHB6-MARK = 'E'.
* ELSEIF 61 <= IT_MCHB6-DAY AND IT_MCHB6-DAY <= 90.
* IT_MCHB6-MARK = 'F'.
* ELSEIF 91 <= IT_MCHB6-DAY AND IT_MCHB6-DAY <= 120.
* IT_MCHB6-MARK = 'G'.
* ELSEIF 121 <= IT_MCHB6-DAY .
* IT_MCHB6-MARK = 'H'.
* ENDIF.
*
* MODIFY IT_MCHB6.
* ENDLOOP.
*
* LOOP AT IT_TAB1.
* LOOP AT IT_MCHB6.
** READ TABLE IT_TAB1 WITH KEY MATNR = IT_MCHB6-MATNR.
** IF SY-SUBRC = 0.
* IF IT_TAB1-MATNR = IT_MCHB6-MATNR.
* CASE IT_MCHB6-MARK.
* WHEN 'A'.
* IT_TAB1-menge1 = IT_MCHB6-MENGE + IT_TAB1-menge1.
** IT_TAB1-netwr1 = IT_MCHB6-DMB

TR.
* WHEN 'B'.
* IT_TAB1-menge2 = IT_MCHB6-MENGE + IT_TAB1-menge2.
** IT_TAB1-netwr2 = IT_MCHB6-DMBTR.
*
* WHEN 'C'.
* IT_TAB1-menge3 = IT_MCHB6-MENGE + IT_TAB1-menge3.
** IT_TAB1-netwr3 = IT_MCHB6-DMBTR.
* WHEN 'D'.
* IT_TAB1-menge4 = IT_MCHB6-MENGE + IT_TAB1-menge4.
** IT_TAB1-netwr4 = IT_MCHB6-DMBTR.
* WHEN 'E'.
* IT_TAB1-menge5 = IT_MCHB6-MENGE + IT_TAB1-menge5.
** IT_TAB1-netwr5 = IT_MCHB6-DMBTR.
* WHEN 'F'.
* IT_TAB1-menge6 = IT_MCHB6-MENGE + IT_TAB1-menge6.
** IT_TAB1-netwr6 = IT_MCHB6-DMBTR.
* WHEN 'G'.
* IT_TAB1-menge7 = IT_MCHB6-MENGE + IT_TAB1-menge7.
** IT_TAB1-netwr7 = IT_MCHB6-DMBTR.
* WHEN 'H'.
* IT_TAB1-menge8 = IT_MCHB6-MENGE + IT_TAB1-menge8.
** IT_TAB1-netwr8 = IT_MCHB6-DMBTR.
* ENDCASE.
* MODIFY IT_TAB1.
* ENDIF.
* ENDLOOP.
* ENDLOOP.
*
*LOOP AT IT_TAB1.
* IF IT_TAB1-ICOLUM1 = 'X'.
* IT_TAB1-LBKUM = IT_TAB1-menge1 + IT_TAB1-menge2 + IT_TAB1-menge3 +
* IT_TAB1-menge4 + IT_TAB1-menge5 + IT_TAB1-menge6 + IT_TAB1-menge7 +
* IT_TAB1-menge8.
* MODIFY IT_TAB1.
* ENDIF.
* ENDLOOP.
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

ENDFORM . "FRM_BUILD_DATA
* PERFORM frm_to_excel.

*&---------------------------------------------------------------------*
*& Form FRM_GET_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text

FORM frm_to_excel .

* SHOW DATA IN EXCEL
DATA: w_fyear(10) TYPE c.
DATA: w_month(20) TYPE c.
DATA: w_tempmonth(3) TYPE c.
DATA: w_count TYPE i.
DATA: w_row TYPE i.
DATA: w_icol(4) TYPE c.
DATA: w_col TYPE i.
DATA: w_tcol(20) TYPE c.
DATA: w_row1 TYPE i.
DATA: wa_fieldcat LIKE REF TO it_fieldcat.

* Download to Excel indicator
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
text = 'Downloading to Excel, Please waiting!'.
* Download the excel template for P&L from server
PERFORM download_template.
* Download data to Front
PERFORM download_data.

* Get line count of i_data
DESCRIBE TABLE i_data LINES w_count.

DESCRIBE TABLE it_tab1 LINES w_row.

CREATE OBJECT w_excel 'EXCEL.APPLICATION'.
PERFORM err_hdl.
SET PROPERTY OF w_excel 'Visible' = 0.

* get list of workbooks, initially empty
CALL METHOD OF w_excel 'WORKBOOKS' = w_mapl.
* Open template file
CALL METHOD OF w_mapl 'Open' = w_map
EXPORTING
#1 = w_ftemplate.

CALL METHOD OF w_map 'WORKSHEETS' = w_sheet
EXPORTING
#1 = c_sheet.

CALL METHOD OF w_sheet 'ACTIVATE'.
* FILL Date
PERFORM fill_range USING 'A1' filetitle.
* PERFORM FILL_RANGE USING 'B2' SY-DATUM.
DATA: uzeit(20) TYPE c.
CONCATENATE

sy-datum '-' sy-uzeit(2) ':' sy-uzeit+2(2) INTO uzeit.
PERFORM fill_range USING 'R2' uzeit.

* fill data file path
PERFORM fill_range USING 'BA4' w_fdata.
PERFORM fill_range USING 'BB4' w_count.
* PERFORM fill_range USING 'BA5' w_row1.
PERFORM fill_range USING 'BA5' w_row.
* PERFORM fill_range USING 'BA6' w_col.

* IF p_a1 <> 'X'.
* w_icol = w_col.
* CONCATENATE '=ADDRESS(1,' w_icol ')' INTO w_tcol.
* PERFORM fill_range USING 'BB6' w_tcol.
* ENDIF.
*
* Run the Excel Macro
CALL METHOD OF w_excel 'RUN'
EXPORTING
#1 = 'Open_text'.

SET PROPERTY OF w_excel 'VISIBLE' = 1 no flush.

FREE w_mapl.
FREE OBJECT w_excel.
* DELETE the data file (txt)
PERFORM delete_file USING w_fdata.

ENDFORM. " FRM_TO_EXCEL

*&---------------------------------------------------------------------*
*& Form ERR_HDL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM err_hdl.
IF sy-subrc <> 0.
WRITE: / 'Commands were not successfully executed:'(010), sy-subrc .
STOP.
ENDIF.
ENDFORM. "ERR_HDL
*&---------------------------------------------------------------------*
*& Form DELETE_FILE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->F_FILENAME text
*----------------------------------------------------------------------*
FORM delete_file USING f_filename .
* Delete the data file c:\temp\ZAPR002.TXT
DATA: w_filename LIKE rlgrap-filename.
w_filename = f_filename.
DATA return.
CALL FUNCTION 'WS_FILE_DELETE'
EXPORTING
file = w_filename
IMPORTING
return = return.
ENDFORM. " delete_file
*&---------------------------------------------------------------------*
*& Form DOWNLOAD_TEMPLATE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM download_template .
* Download the excel template for P&L from server
DATA: w_exist. "Check the file exist or not

CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
key = w_formkey
destination = w_ftemplate
CHANGING
temp = w_temp_file.
* ENDIF.
ENDFORM. " DOWNLOAD_TEMPLATE

*&---------------------------------------------------------------------*
*& Form FILL_RANGE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->VALUE(F_RANGE) text
* -->VALUE(F_VALUE) text

*----------------------------------------------------------------------*
FORM fill_range USING value(f_range)
value(f_value).
CALL METHOD OF w_sheet 'Range' = w_rangeobj
EXPORTING
#1 = f_range.
SET PROPERTY OF w_rangeobj 'Value' = f_value.
ENDFORM. " sub_fill_range
*&---------------------------------------------------------------------*
*& Form DOWNLOAD_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM download_data .
DATA: flag,flag1.
DATA: w_tempfile TYPE string.
DATA: w_lrange TYPE char4,
w_rrange TYPE char4.
DATA: w_row TYPE char4,
w_row1 TYPE char4,
w_column TYPE char4,
w_startrow TYPE char4 VALUE '5', "In Excel start row
w_startcolumn TYPE char4 VALUE '1', "Column F
w_itemno TYPE char4.
* DATA: day TYPE i.
* DATA: week TYPE i.
DATA: l_rows TYPE i.
DATA: l_col TYPE i.
DATA: ls_menge TYPE menge_d,
ls_dmbtr TYPE dmbtr,
ls_matnr TYPE matnr.
w_tempfile = w_fdata.
REFRESH i_data.
* SORT it_tab BY matnr .
l_rows = w_startrow.
l_col = w_startcolumn.

LOOP AT it_tab1.
AT END OF icolum.
flag = 'X'.
ENDAT.

* ls_menge = ls_menge + it_tab-menge.
*
* ls_dmbtr = ls_menge * it_tab-dmbtr.


IF flag = 'X'.
IF ls_matnr = it_tab1-matnr.
l_rows = l_rows - 1.
ENDIF.

i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '1'.
i_data-value = it_tab1-matnr. "数量
APPEND i_data. CLEAR i_data.
i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '2'.
i_data-value = it_tab1-maktx. "数量
APPEND i_data. CLEAR i_data.

i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '3'.
i_data-value = it_tab1-meins. "数量
APPEND i_data. CLEAR i_data.

i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '4'.
i_data-value = it_tab1-LBKUM. "数量
APPEND i_data. CLEAR i_data.

i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '5'.
i_data-value = it_tab1-SALK3. "数量
APPEND i_data. CLEAR i_data.

* CASE it_tab-icolum.
* WHEN 'A'.
i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '6'.
i_data-value = it_tab1-menge1. "数量
APPEND i_data. CLEAR i_data.

i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '7'.
i_data-value = it_tab1-netwr1 . "金额
APPEND i_data. CLEAR i_data.

* WHEN 'B'.

i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '8'.
i_data-value = it_tab1-menge2. "数量
APPEND i_data. CLEAR i_data.

i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '9'.
i_data-value = it_tab1-netwr2 . "金额
APPEND i_data. CLEAR i_data.
* WHEN 'C'.
i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '10'.
i_data-value = it_tab1-menge3. "数量
APPEND i_data. CLEAR i_data.

i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '11'.
i_data-value = it_tab1-netwr3 . "金额
APPEND i_data. CLEAR i_data.
* WHEN 'D'.
i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '12'.
i_data-value = it_tab1-menge4. "数量
APPEND i_data. CLEAR i_data.

i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '13'.
i_data-value = it_tab1-netwr4 . "金额
APPEND i_data. CLEAR i_data.
* WHEN 'E'.
i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '14'.
i_data-value = it_tab1-menge5. "数量
APPEND i_data. CLEAR i_data.

i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '15'.
i_data-value = it_tab1-netwr5 . "金额
APPEND i_data. CLEAR i_data.
* WHEN 'F'.
i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '16'.
i_data-value = it_tab1-menge6. "数量
APPEND i_data. CLEAR i_data.

i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '17'.
i_data-value = it_tab1-netwr6 . "金额
APPEND i_data. CLEAR i_data.

* WHEN 'G'.
i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '18'.
i_data-value = it_tab1-menge7. "数量
APPEND i_data. CLEAR i_data.

i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '19'.
i_data-value = it_tab1-netwr7 . "金额
APPEND i_data. CLEAR i_data.
* WHEN 'H'.
i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '20'.
i_data-value = it_tab1-menge8. "数量
APPEND i_data. CLEAR i_data.

i_data-sheet = c_sheet.
i_data-row = l_rows.
i_data-column = '21'.
i_data-value = it_tab1-netwr8 . "金额
APPEND i_data. CLEAR i_data.
* WHEN OTHERS.
* ENDCASE.

CLEAR flag.
l_rows = l_rows + 1.
l_col = w_startcolumn.
ls_matnr = it_tab1-matnr.
CLEAR: ls_menge ,ls_dmbtr.
ENDIF.
ENDLOOP.


CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = w_tempfile
write_field_separator = cl_abap_char_utiliti

es=>horizontal_tab
codepage = '8400'
TABLES
data_tab = i_data.

ENDFORM. " DOWNLOAD_DATA