*&---------------------------------------------------------------------*
*& Report ZRPT_MMCO_HISCMP
*&
*&---------------------------------------------------------------------*
*&AUTHOR GAVIN
*&DATE:20220219
*&FROM mm 采购 陈洲
*& TCODE:
*&---------------------------------------------------------------------*
REPORT ZRPT_MMCO_HISCMP1.
TABLES:MBEW,A017,MBEWH,EKKO,EKPO,LFA1,MARC.
PARAMETERS:PDATE LIKE SY-DATUM OBLIGATORY DEFAULT sy-datum ."价格参考日期
SELECT-OPTIONS:S_DATE FOR SY-DATUM OBLIGATORY DEFAULT sy-datum to sy-datum."统计期间
SELECT-OPTIONS:S_WERKS FOR MARC-WERKS OBLIGATORY DEFAULT '1000'."工厂
SELECT-OPTIONS:S_MATNR FOR MARC-MATNR ."物料
SELECT-OPTIONS:S_LIFNR FOR LFA1-LIFNR ."供应商
DATA: IT_FIELDCAT TYPE LVC_T_FCAT,
WA_LAYOUT TYPE LVC_S_LAYO.
DATA:LT001W LIKE T001W OCCURS 0 WITH HEADER LINE.
DATA:STDATUM LIKE SY-DATUM,
STUZEIT LIKE SY-UZEIT.
DATA: IT_OUTPUT TYPE REF TO DATA,
WA_OUTPUT TYPE REF TO DATA.
FIELD-SYMBOLS: <IT_OUTPUT> TYPE STANDARD TABLE,
<WA_OUTPUT>.
START-OF-SELECTION.
STDATUM = SY-DATUM.
STUZEIT = SY-UZEIT.
* 建立输出表列
PERFORM BUILD_FIELDCAT.
* 创建输出表
PERFORM CREATE_DYNAMIC_TABLE.
PERFORM GETDATA.
PERFORM WRITERPTRUNRECORD.
PERFORM CALL_ALV_DISPLAY.
*&---------------------------------------------------------------------*
*& FORM BUILD_FIELDCAT
*&---------------------------------------------------------------------*
* TEXT
*----------------------------------------------------------------------*
FORM BUILD_FIELDCAT .
DATA: L_TITLE TYPE TEXT40.
DATA: L_TITLES TYPE TEXT40.
DATA: L_COLNAM TYPE TEXT40.
DATA: L_CO(2) TYPE C.
DATA: PDATESRC TYPE SY-DATUM,
PDATEEND TYPE SY-DATUM.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING 'WERKS' 'WERKS' 'MARC' '' '工厂' '' 'C100' 'X' '1' 'X'.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING 'MATNR' 'MATNR' 'MARA' '' '物料' '' 'C100' 'X' '1' 'X'.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING 'MAKTX' '' '' '' '物料描述' '' 'C100' 'X' '1' ''.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING 'MATKL' 'MATKL' 'MARA' '' '物料组' '' 'C100' 'X' '1' 'X'.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING 'WGBEZ' 'WGBEZ' 'T023T' '' '物料组描述' '' 'C100' 'X' '1' 'X'.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING 'KBETR' 'KBETR' 'KONP' '' '理论单价(单价配额加权)' '' 'C100' 'X' '1' ''.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING 'KPEIN' 'KPEIN' 'KONP' '' '价格单位' '' 'C100' 'X' '1' ''.
PDATESRC = S_DATE-LOW. PDATESRC+6(2) = '01'.
CALL FUNCTION 'LAST_DAY_OF_MONTHS'
EXPORTING
day_in = S_DATE-HIGH "输入日期
IMPORTING
last_day_of_month = PDATEEND "返回最后一天日期:20160131
EXCEPTIONS
day_in_no_date = 1
OTHERS = 2.
WHILE PDATESRC <= PDATEEND.
"采购收货数量
L_COLNAM = PDATESRC+0(6) && 'MENGE'.
L_TITLE = PDATESRC+0(6) && '采购收货数量'.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING L_COLNAM 'MENGE' 'EKPO' '' L_TITLE '' 'C100' '' '1' ''.
"采购理论金额
L_COLNAM = PDATESRC+0(6) && 'KBETRPLAS'.
L_TITLE = PDATESRC+0(6) && '理论金额'.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING L_COLNAM 'BRTWR' 'EKPO' '' L_TITLE '' 'C100' '' '1' ''.
"采购实际金额
L_COLNAM = PDATESRC+0(6) && 'KBETRRELS'.
L_TITLE = PDATESRC+0(6) && '实际金额'.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING L_COLNAM 'BRTWR' 'EKPO' '' L_TITLE '' 'C100' '' '1' ''.
"采购降本金额
L_COLNAM = PDATESRC+0(6) && 'KBETRDLTS'.
L_TITLE = PDATESRC+0(6) && '参考降本金额'.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING L_COLNAM 'BRTWR' 'EKPO' '' L_TITLE '' 'C100' '' '1' ''.
"* 下个月
CALL FUNCTION 'RP_CALC_DATE_IN_INTERVAL'
EXPORTING
DATE = PDATESRC " DAYS = N ”天数加N或者减N 不加也不减 写0
DAYS = 0 "天数加N或者减N 不加也不减 写0
MONTHS = 1 "月份加N或者减N 不加也不减 写0
SIGNUM = '+' "可以是+或者-
YEARS = 0"年份加N或者减N 不加也不减 写0
IMPORTING
CALC_DATE = PDATESRC."得到加减之后的日期
ENDWHILE.
ENDFORM. "BUILD_FIELDCAT
*&---------------------------------------------------------------------*
*& FORM F_SET_FIELDCAT
*&---------------------------------------------------------------------*
* TEXT
*----------------------------------------------------------------------*
* -->CT_FIELDCAT TEXT
* -->UV_FIELDNAME TEXT
* -->UV_REF_FIELD TEXT
* -->UV_REF_TABLE TEXT
* -->UV_TEXT TEXT
*----------------------------------------------------------------------*
FORM SET_FIELDCAT TABLES CT_FIELDCAT
USING UV_FIELDNAME
UV_REF_FIELD
UV_REF_TABLE
UV_EDIT_MASK
UV_TEXT
UV_TEXTS
UV_EMPHASIZE
UV_FIX_COLUMN
UV_HIER_CPOS
UV_NOZERO .
DATA: LW_FIELD TYPE LVC_S_FCAT.
CLEAR: LW_FIELD.
LW_FIELD-NO_ZERO = UV_NOZERO.
LW_FIELD-HIER_CPOS = UV_HIER_CPOS.
LW_FIELD-FIX_COLUMN = UV_FIX_COLUMN.
LW_FIELD-FIELDNAME = UV_FIELDNAME.
LW_FIELD-REF_FIELD = UV_REF_FIELD.
LW_FIELD-REF_TABLE = UV_REF_TABLE.
LW_FIELD-EDIT_MASK = UV_EDIT_MASK.
LW_FIELD-EMPHASIZE = UV_EMPHASIZE.
LW_FIELD-COLTEXT = UV_TEXT.
LW_FIELD-SELTEXT = UV_TEXT.
LW_FIELD-SCRTEXT_L = UV_TEXT.
LW_FIELD-SCRTEXT_M = UV_TEXT.
LW_FIELD-SCRTEXT_S = UV_TEXTS.
LW_FIELD-FIX_COLUMN = UV_FIX_COLUMN.
APPEND LW_FIELD TO CT_FIELDCAT.
ENDFORM. " F_SET_FIELDCAT
*&---------------------------------------------------------------------*
*& FORM CALL_ALV_DISPLAY
*&---------------------------------------------------------------------*
* TEXT
*----------------------------------------------------------------------*
* --> P1 TEXT
* <-- P2 TEXT
*----------------------------------------------------------------------*
FORM CALL_ALV_DISPLAY .
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
EXPORTING
I_CALLBACK_PROGRAM = SY-REPID
IS_LAYOUT_LVC = WA_LAYOUT
IT_FIELDCAT_LVC = IT_FIELDCAT
I_DEFAULT = ''
TABLES
T_OUTTAB = <IT_OUTPUT>
EXCEPTIONS
PROGRAM_ERROR = 1
OTHERS = 2.
IF SY-SUBRC <> 0.
ENDIF.
ENDFORM. "CALL_ALV_DISPLAY
*&---------------------------------------------------------------------*
*& FORM CREATE_DYNAMIC_TABLE
*&---------------------------------------------------------------------*
* TEXT
*----------------------------------------------------------------------*
* --> P1 TEXT
* <-- P2 TEXT
*----------------------------------------------------------------------*
FORM CREATE_DYNAMIC_TABLE .
CALL METHOD CL_ALV_TABLE_CREATE=>CREATE_DYNAMIC_TABLE
EXPORTING
IT_FIELDCATALOG = IT_FIELDCAT
IMPORTING
EP_TABLE = IT_OUTPUT
EXCEPTIONS
GENERATE_SUBPOOL_DIR_FULL = 1
OTHERS = 2.
IF SY-SUBRC <> 0.
ENDIF.
ASSIGN IT_OUTPUT->* TO <IT_OUTPUT>.
CREATE DATA WA_OUTPUT LIKE LINE OF <IT_OUTPUT>.
ASSIGN WA_OUTPUT->* TO <WA_OUTPUT>.
ENDFORM. "CREATE_DYNAMIC_TABLE
*&---------------------------------------------------------------------*
*& FORM WRITERPTRUNRECORD
*&---------------------------------------------------------------------*
* TEXT
*----------------------------------------------------------------------*
FORM WRITERPTRUNRECORD.
GET TIME.
CALL FUNCTION 'ZSYS_RPT_RUNRECORD'
EXPORTING
TCODE = SY-TCODE
PROGRAMM = SY-CPROG
DATUMRST = STDATUM
UZEITRST = STUZEIT
BATCH = SY-BATCH
DATUMREND = SY-DATUM
UZEITREND = SY-UZEIT
DATUMC = '20220316'
RUNNER = SY-UNAME
RPTSRC = 'MM陈洲'.
ENDFORM. "WRITERPTRUNRECORD
*&---------------------------------------------------------------------*
*& FORM GETDATA
*&---------------------------------------------------------------------*
* TEXT
*----------------------------------------------------------------------*
* --> P1 TEXT
* <-- P2 TEXT
*----------------------------------------------------------------------*
FORM GETDATA .
DATA:INDEX TYPE I.
data:compindex TYPE i.
DATA:LVERPR LIKE MBEW-VERPR.
DATA:PDATESRC TYPE SY-DATUM,
PDATEEND TYPE SY-DATUM.
DATA:MLAST TYPE SY-DATUM.
data:lKNUMH type a017-KNUMH.
data:lPEINH TYPE KONP-KPEIN,
lKBETR LIKE KONP-KBETR.
DATA:LMENGE TYPE MSEG-MENGE,ldmbter TYPE mseg-dmbtr.
DATA:ALLMENGE TYPE MSEG-MENGE,
ALLBRTWR TYPE EKPO-BRTWR,
KBETRAVE TYPE KONP-KBETR.
types:BEGIN OF WJZITAB ,
WERKS LIKE A017-WERKS,
MATNR LIKE A017-MATNR,
MAKTX LIKE MAKT-MAKTX,
MATKL like mara-matkl,
WGBEZ LIKE T023T-WGBEZ,
KBETR LIKE KONP-KBETR,
KPEIN like KONP-KPEIN,
QUOTE LIKE EQUP-QUOTE,"配额
END OF WJZITAB.
FIELD-SYMBOLS:<FS1> TYPE WJZITAB.
DATA:HWJZITAB TYPE TABLE OF WJZITAB WITH HEADER LINE WITH KEY WERKS MATNR.
DATA:BEGIN OF LOC_EINA OCCURS 0,
WERKS LIKE EINE-WERKS,
INFNR LIKE EINA-INFNR,
NETPR LIKE EINE-NETPR,
PEINH LIKE EINE-PEINH,
LIFNR LIKE EINA-LIFNR,
MATNR LIKE EINA-MATNR,
APLFZ LIKE EINE-APLFZ,"交货时间以天计
QUOTE LIKE EQUP-QUOTE,"配额
END OF LOC_EINA,
LOC_QUOTE LIKE EQUP-QUOTE.
FIELD-SYMBOLS <FIELDP>.
SELECT EINE~WERKS EINA~INFNR EINE~NETPR EINE~PEINH LIFNR MATNR APLFZ INTO TABLE LOC_EINA
FROM EINA JOIN EINE ON EINE~INFNR = EINA~INFNR
WHERE EINA~MATNR IN S_MATNR AND EINE~WERKS IN S_WERKS AND LIFNR in s_lifnr and
EINE~LOEKZ <> 'X' AND EINE~ERDAT <= PDATE AND EINE~PRDAT >= PDATE.
LOOP AT LOC_EINA.
"读取配额
SELECT SINGLE QUOTE INTO LOC_EINA-QUOTE
FROM EQUK JOIN EQUP ON ( EQUK~QUNUM = EQUP~QUNUM )
WHERE MATNR = LOC_EINA-MATNR AND EQUK~BDATU GT SY-DATUM AND
WERKS = LOC_EINA-WERKS AND EQUP~LIFNR = LOC_EINA-LIFNR.
SELECT SINGLE KNUMH INTO LKNUMH FROM A017
WHERE A017~LIFNR = LOC_EINA-LIFNR AND A017~MATNR = LOC_EINA-MATNR AND WERKS = LOC_EINA-WERKS AND DATBI >= PDATE
AND DATAB <= PDATE.
IF LKNUMH IS NOT INITIAL.
SELECT SINGLE KONP~KBETR KONP~KPEIN INTO (lKBETR,lPEINH)
FROM KONP
WHERE KONP~KNUMH = LKNUMH.
if lKBETR is not INITIAL and lPEINH is not INITIAL.
LOC_EINA-NETPR = lKBETR.
LOC_EINA-PEINH = lPEINH.
endif.
ENDIF.
MODIFY LOC_EINA.
ENDLOOP.
SORT LOC_EINA BY MATNR.
LOOP AT LOC_EINA.
READ TABLE HWJZITAB ASSIGNING <FS1> WITH TABLE KEY WERKS = LOC_EINA-WERKS MATNR = LOC_EINA-MATNR.
IF SY-SUBRC <> 0.
APPEND INITIAL LINE TO HWJZITAB ASSIGNING <FS1>.
ENDIF.
<FS1>-WERKS = LOC_EINA-WERKS.
<FS1>-MATNR = LOC_EINA-MATNR.
<FS1>-QUOTE = <FS1>-QUOTE + LOC_EINA-QUOTE.
<FS1>-KPEIN = LOC_EINA-PEINH.
ENDLOOP.
LOOP AT LOC_EINA.
READ TABLE HWJZITAB ASSIGNING <FS1> WITH TABLE KEY WERKS = LOC_EINA-WERKS MATNR = LOC_EINA-MATNR.
"物料描述
SELECT SINGLE MAKTX INTO <FS1>-MAKTX FROM MAKT WHERE MATNR = <FS1>-MATNR AND SPRAS = 1.
SELECT SINGLE MARA~MATKL T023T~WGBEZ INTO (<FS1>-MATKL,<FS1>-WGBEZ)
FROM MARA JOIN T023T ON MARA~MATKL = T023T~MATKL AND T023T~SPRAS = 1
WHERE MARA~MATNR = <FS1>-MATNR .
IF <FS1>-QUOTE > 0.
<FS1>-KBETR = <FS1>-KBETR + LOC_EINA-QUOTE / <FS1>-QUOTE * LOC_EINA-NETPR.
ELSE.
<FS1>-KBETR = LOC_EINA-NETPR.
ENDIF.
ENDLOOP.
LOOP AT HWJZITAB.
PDATESRC = S_DATE-LOW. PDATESRC+6(2) = '01'.
CALL FUNCTION 'LAST_DAY_OF_MONTHS'
EXPORTING
DAY_IN = S_DATE-HIGH "输入日期
IMPORTING
LAST_DAY_OF_MONTH = PDATEEND "返回最后一天日期:20160131
EXCEPTIONS
DAY_IN_NO_DATE = 1
OTHERS = 2.
MOVE-CORRESPONDING HWJZITAB to <WA_OUTPUT>.
INDEX = 0. CLEAR ALLMENGE.CLEAR ALLBRTWR.
WHILE PDATESRC <= PDATEEND.
"求得月初月末
CLEAR LMENGE .CLEAR LDMBTER.
CALL FUNCTION 'LAST_DAY_OF_MONTHS'
EXPORTING
DAY_IN = PDATESRC "输入日期
IMPORTING
LAST_DAY_OF_MONTH = MLAST "返回最后一天日期:20160131
EXCEPTIONS
DAY_IN_NO_DATE = 1
OTHERS = 2.
"对应月份实际收货
COMPINDEX = 8 + INDEX * 4.
ASSIGN COMPONENT COMPINDEX OF STRUCTURE <WA_OUTPUT> TO <FIELDP>.
PERFORM COUNTMENGE USING HWJZITAB-MATNR HWJZITAB-WERKS PDATESRC MLAST CHANGING LMENGE LDMBTER."实际金额
<FIELDP> = LMENGE.
"理论金额
COMPINDEX = COMPINDEX + 1.
ASSIGN COMPONENT COMPINDEX OF STRUCTURE <WA_OUTPUT> TO <FIELDP>.
<FIELDP> = lmenge * HWJZITAB-KBETR / HWJZITAB-KPEIN.
COMPINDEX = COMPINDEX + 1.
ASSIGN COMPONENT COMPINDEX OF STRUCTURE <WA_OUTPUT> TO <FIELDP>.
<FIELDP> = LDMBTER.
"浮动金额
COMPINDEX = COMPINDEX + 1.
ASSIGN COMPONENT COMPINDEX OF STRUCTURE <WA_OUTPUT> TO <FIELDP>.
<FIELDP> = LDMBTER - LMENGE * HWJZITAB-KBETR / HWJZITAB-KPEIN .
"下个月
CALL FUNCTION 'RP_CALC_DATE_IN_INTERVAL'
EXPORTING
DATE = PDATESRC " DAYS = N ”天数加N或者减N 不加也不减 写0
DAYS = 0 "天数加N或者减N 不加也不减 写0
MONTHS = 1 "月份加N或者减N 不加也不减 写0
SIGNUM = '+' "可以是+或者-
YEARS = 0"年份加N或者减N 不加也不减 写0
IMPORTING
CALC_DATE = PDATESRC."得到加减之后的日期
INDEX = INDEX + 1.
ENDWHILE.
APPEND <WA_OUTPUT> TO <IT_OUTPUT>.
ENDLOOP.
ENDFORM. " GETDATA
*&---------------------------------------------------------------------*
*& Form countmenge
*&---------------------------------------------------------------------*
* 计算周期内的收货 - 相应的冲销
*----------------------------------------------------------------------*
* -->PLIFNR text
* -->PMATNR text
* -->PWERKS text
* -->PDATESRC text
* -->PDATEEND text
* -->PCOUNT text
*----------------------------------------------------------------------*
FORM COUNTMENGE USING PMATNR LIKE MARA-MATNR PWERKS LIKE MSEG-WERKS
PDATESRC TYPE SY-DATUM PDATEEND TYPE SY-DATUM CHANGING PCOUNT TYPE MSEG-MENGE PDMBTR TYPE MSEG-DMBTR.
DATA:SUM101 TYPE MSEG-MENGE,
SUM102 TYPE MSEG-MENGE,
DMB101 TYPE mseg-DMBTR,
DMB102 TYPE mseg-DMBTR.
DATA:PMJAHR TYPE MSEG-MJAHR.
PMJAHR = PDATESRC+0(4).
"收货数量
SELECT SUM( MENGE ) sum( BUALT ) INTO (SUM101,DMB101)
FROM MSEG
WHERE MATNR = PMATNR AND LIFNR in S_LIFNR AND BWART = '101' AND WERKS = PWERKS AND MJAHR = PMJAHR
AND BUDAT_MKPF >= PDATESRC AND BUDAT_MKPF <= PDATEEND .
"冲销数量
SELECT SUM( MENGE ) sum( BUALT ) INTO (SUM102,DMB102)
FROM mseg
WHERE matnr = PMATNR AND LIFNR in S_LIFNR AND bwart = '102' AND werks = pwerks and mjahr = pmjahr
and BUDAT_MKPF >= PDATESRC AND BUDAT_MKPF <= PDATEEND .
PCOUNT = sum101 - sum102.
PDMBTR = DMB101 - DMB102.
ENDFORM. "COUNTMENGE