实际情况
逻辑简述:基准合同价取 指定日期有效的采购合同价 A017
当月的价格取,当月采购订单的价格
数量 = 收货数量 - 冲销数量 sum101 - sum102
上代码:
*&---------------------------------------------------------------------*
*& Report ZRPT_MMCO_HISCMP
*&
*&---------------------------------------------------------------------*
*&AUTHOR GAVIN
*&DATE:20220219
*&FROM mm 采购 ZYZ
*& TCODE:
*&---------------------------------------------------------------------*
REPORT ZRPT_MMCO_HISCMP.
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 'LIFNR' 'LIFNR' 'LFA1' '' '供应商' '' 'C100' 'X' '1' 'X'.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING 'NAME1' '' '' '' '供应商名称' '' 'C100' 'X' '1' ''.
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) && 'KBETR'.
L_TITLE = PDATESRC+0(6) && '系统维护价格'.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING L_COLNAM 'KBETR' 'KONP' '' L_TITLE '' 'C100' '' '1' ''.
"单价浮动
L_COLNAM = PDATESRC+0(6) && 'KBETRDLT'.
L_TITLE = PDATESRC+0(6) && '单价浮动'.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING L_COLNAM 'KBETR' 'KONP' '' L_TITLE '' 'C100' '' '1' ''.
"采购收货数量
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) && '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.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING 'MENGESUM' 'MENGE' 'EKPO' '' '累计收货数量' '' 'C100' '' '1' ''.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING 'KBETRAVE' 'KBETR' 'KONP' '' '平均浮动价格' '' 'C100' '' '1' ''.
PERFORM SET_FIELDCAT TABLES IT_FIELDCAT USING 'KBETRDLTALL' 'BRTWR' 'EKPO' '' '累计采购浮动' '' 'C100' '' '1' ''.
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 = '20220222'
RUNNER = SY-UNAME
RPTSRC = 'MM ZYZ'.
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:LMENGE TYPE MSEG-MENGE.
DATA:ALLMENGE TYPE MSEG-MENGE,
ALLBRTWR TYPE EKPO-BRTWR,
KBETRAVE TYPE KONP-KBETR.
DATA:BEGIN OF WJZITAB OCCURS 0,
WERKS LIKE A017-WERKS,
MATNR LIKE A017-MATNR,
LIFNR LIKE A017-LIFNR,
KNUMH like A017-KNUMH,
MAKTX LIKE MAKT-MAKTX,
NAME1 LIKE LFA1-NAME1,
KBETR LIKE KONP-KBETR,
KPEIN like KONP-KPEIN,
END OF WJZITAB.
FIELD-SYMBOLS <FIELDP>.
SELECT A017~WERKS A017~MATNR A017~LIFNR A017~KNUMH
INTO TABLE WJZITAB
FROM A017
WHERE A017~WERKS IN S_WERKS AND A017~MATNR IN S_MATNR AND A017~LIFNR IN S_LIFNR
AND A017~DATBI >= PDATE AND A017~DATAB <= PDATE.
LOOP AT WJZITAB.
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.
"物料描述
SELECT SINGLE MAKTX INTO WJZITAB-MAKTX FROM MAKT WHERE MATNR = WJZITAB-MATNR AND SPRAS = 1.
"供应商名称
SELECT SINGLE NAME1 INTO WJZITAB-NAME1 FROM LFA1 WHERE LIFNR = WJZITAB-LIFNR.
"基准价格获取
SELECT SINGLE KBETR KPEIN INTO (WJZITAB-KBETR,WJZITAB-KPEIN ) FROM KONP WHERE KNUMH = WJZITAB-KNUMH.
MOVE-CORRESPONDING WJZITAB to <WA_OUTPUT>.
INDEX = 0. CLEAR ALLMENGE.CLEAR ALLBRTWR.
WHILE PDATESRC <= PDATEEND.
"求得月初月末
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>.
"考虑当月可能没有采购订单,但是有跨月收货,则逆序取最后一个 采购订单价格
SELECT NETPR INTO LVERPR
UP TO 1 ROWS
FROM EKKO
JOIN EKPO ON EKKO~EBELN = EKPO~EBELN
WHERE EKKO~LIFNR = WJZITAB-LIFNR AND EKPO~WERKS = WJZITAB-WERKS AND MATNR = WJZITAB-MATNR
AND EKKO~AEDAT >= PDATE AND EKKO~AEDAT <= MLAST
ORDER BY ekko~AEDAT DESCENDING .
<FIELDP> = LVERPR.
ENDSELECT.
"如果还是没有 ,则且是第一个统计期间,则直接为基准价格
IF ( <FIELDP> IS INITIAL OR <FIELDP> <= 0 ) AND INDEX = 0.
LVERPR = WJZITAB-KBETR.
<FIELDP> = LVERPR.
ENDIF.
"浮动价格
COMPINDEX = COMPINDEX + 1.
ASSIGN COMPONENT COMPINDEX OF STRUCTURE <WA_OUTPUT> TO <FIELDP>.
<FIELDP> = LVERPR - WJZITAB-KBETR.
"对应月份实际收货
COMPINDEX = COMPINDEX + 1.
ASSIGN COMPONENT COMPINDEX OF STRUCTURE <WA_OUTPUT> TO <FIELDP>.
PERFORM COUNTMENGE USING WJZITAB-LIFNR WJZITAB-MATNR WJZITAB-WERKS PDATESRC MLAST CHANGING lmenge.
<FIELDP> = lmenge.
ALLMENGE = ALLMENGE + lmenge.
"浮动金额
COMPINDEX = COMPINDEX + 1.
ASSIGN COMPONENT COMPINDEX OF STRUCTURE <WA_OUTPUT> TO <FIELDP>.
<FIELDP> = LMENGE * ( LVERPR - WJZITAB-KBETR ).
ALLBRTWR = ALLBRTWR + LMENGE * ( LVERPR - WJZITAB-KBETR ).
"下个月
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.
COMPINDEX = 8 + INDEX * 4.
ASSIGN COMPONENT COMPINDEX OF STRUCTURE <WA_OUTPUT> TO <FIELDP>.
<FIELDP> = ALLMENGE.
COMPINDEX = COMPINDEX + 1.
ASSIGN COMPONENT COMPINDEX OF STRUCTURE <WA_OUTPUT> TO <FIELDP>.
IF ALLMENGE <> 0.
KBETRAVE = ALLBRTWR / ALLMENGE.
<FIELDP> = KBETRAVE.
ENDIF.
COMPINDEX = COMPINDEX + 1.
ASSIGN COMPONENT COMPINDEX OF STRUCTURE <WA_OUTPUT> TO <FIELDP>.
<FIELDP> = ALLBRTWR.
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 PLIFNR LIKE lfa1-LIFNR PMATNR LIKE mara-MATNR PWERKS LIKE mseg-WERKS
PDATESRC TYPE SY-DATUM PDATEEND TYPE SY-DATUM CHANGING PCOUNT TYPE mseg-menge.
data:SUM101 TYPE mseg-menge,
sum102 TYPE mseg-menge.
DATA:pmjahr TYPE mseg-MJAHR.
pmjahr = PDATESRC+0(4).
"收货数量
select sum( menge ) into sum101
from mseg
where matnr = PMATNR AND lifnr = PLIFNR AND bwart = '101' AND werks = pwerks and mjahr = pmjahr
and BUDAT_MKPF >= PDATESRC AND BUDAT_MKPF <= PDATEEND .
"冲销数量
SELECT SUM( menge ) INTO sum102
FROM mseg
WHERE matnr = PMATNR AND lifnr = PLIFNR AND bwart = '102' AND werks = pwerks and mjahr = pmjahr
and BUDAT_MKPF >= PDATESRC AND BUDAT_MKPF <= PDATEEND .
PCOUNT = sum101 - sum102.
ENDFORM. "COUNTMENGE