1.通过inner join 将两个具有相同字段的父表和字表连接起来，减少代码的繁杂度，增加搜索精度和速度，减少对内存空间的占用。
代码语言：javascript复制&-----------------------------------------------------------------------------------------------------*
*& CHANGE HISTORY for XX project *
*&-----------------------------------------------------------------------------------------------------*
*& DATE | PROGRAMMER | FLAG *
*& 2022/8/13 | Lee | XX-BW *
*------------------------------------------------------------------------------------------------------*
REPORT ZT006.
*Declaration tables 数据库表的申明
TABLES:EKKO, "采购凭证抬头
EKPO, "采购凭证项目
KONV, "条件（事务数据）
MSEG, "凭证段：物料
MKPF. "抬头：物料凭证
DATA:
"采购凭证项目
BEGIN OF GIT_EKPO OCCURS 0,
EBELN TYPE EKPO-EBELN, "采购凭证号
EBELP TYPE EKPO-EBELP, "采购凭证的项目编号
BUKRS TYPE EKKO-BUKRS, "公司代码
BSART TYPE EKKO-BSART, "采购凭证类型
KNUMV TYPE EKKO-KNUMV, "单据条件数
MATNR TYPE EKPO-MATNR, "物料号
TXZ01 TYPE EKPO-TXZ01, "短文本
END OF GIT_EKPO,
"条件（事务数据）
BEGIN OF GIT_KONV OCCURS 0,
KNUMV TYPE KONV-KNUMV, "单据条件数
KPOSN TYPE KONV-KPOSN, "条件项目号
MATNR TYPE KONV-STUNR, "步骤编号
ZAEHK TYPE KONV-ZAEHK, "条件计数器
KBETR TYPE KONV-KBETR, "基本计量单位
KSCHL TYPE KONV-KSCHL, "条件类型
END OF GIT_KONV,
"凭证段：物料
BEGIN OF GIT_MSEG OCCURS 0,
MBLNR TYPE MSEG-MBLNR, "物料凭证编号
MJAHR TYPE MSEG-MJAHR, "物料凭证年度
ZEILE TYPE MSEG-ZEILE, "物料凭证中的项目
MENGE TYPE MSEG-MENGE, "数量
MEINS TYPE MSEG-MEINS, "基本计量单位
EBELN TYPE MSEG-EBELN, "采购凭证号
EBELP TYPE MSEG-EBELP, "采购凭证的项目编号
MATNR TYPE MSEG-MATNR, "物料号
WAERS TYPE MSEG-WAERS, "货币码
BUDAT TYPE MKPF-BUDAT, "凭证中的过帐日期
END OF GIT_MSEG,
"结果表
BEGIN OF GIT_RESULT OCCURS 0,
EBELN TYPE MSEG-EBELN, "采购凭证号
EBELP TYPE EKPO-EBELP, "采购凭证的项目编号
BSART TYPE EKKO-BSART, "采购凭证类型
MATNR TYPE EKPO-MATNR, "物料号
TXZ01 TYPE EKPO-TXZ01, "短文本
BUDAT TYPE MKPF-BUDAT, "凭证中的过帐日期
MENGE TYPE MSEG-MENGE, "数量
MEINS TYPE MSEG-MEINS, "单位
JIA01 TYPE KONV-KBETR, "价格
JIN01 TYPE KONV-KBETR, "金额
JIA02 TYPE KONV-KBETR, "折扣
JIN02 TYPE KONV-KBETR, "折扣金额
JIN03 TYPE KONV-KBETR, "最终金额
HUOBI TYPE MSEG-WAERS, "货币单位
END OF GIT_RESULT.
*Ranges
"RANGES: r_hkont FOR bseg-hkont, "G/L Account
*Constants
"CONSTANTS:
" c_awtyp_vbrr TYPE bkpf-awtyp VALUE 'VBRR', "Refer. Transac
TYPE-POOLS: SLIS. "引用ALV显示功能所需要的类型库
DATA: GT_FIELDCAT TYPE SLIS_T_FIELDCAT_ALV,
"参考slis类型库中的t_fieldcat_alv定义变量。
PRG LIKE SY-REPID.
************************************************************************
* Selection screen *
************************************************************************
SELECTION-SCREEN: BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.
*Input screen: selection options
SELECT-OPTIONS:
S_BUKRS FOR EKKO-BUKRS OBLIGATORY, "公司代码
S_BUDAT FOR MKPF-BUDAT, "过账日期
S_BSART FOR EKKO-BSART, "采购凭证类型
S_EBELN FOR EKPO-EBELN, "采购凭证号
S_MATNR FOR EKPO-MATNR. "物料号
SELECTION-SCREEN: END OF BLOCK B1.
************************************************************************
* Start of selection *
************************************************************************
START-OF-SELECTION. "程序真正从这里开始执行
* Main process
PERFORM FRM_MAIN_PROCESS.
*&---------------------------------------------------------------------*
*& Form frm_main_process
*&---------------------------------------------------------------------*
* main process
*----------------------------------------------------------------------*
FORM FRM_MAIN_PROCESS .
* 取数
PERFORM FRM_GET_DATA.
* 整合数据
PERFORM FRM_MERGE_DATA.
* 显示数据
PERFORM FRM_DISPLAY_DATA.
ENDFORM. " frm_main_process
*----------------------------------------------------------------------*
***INCLUDE ZT005_FRM_GET_DATAF01 .
*----------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Form FRM_GET_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM FRM_GET_DATA .
"读取数据 SQL结构化查询语句
SELECT A~EBELN "采购凭证号
A~BUKRS "公司代码
A~BSART "采购凭证类型
A~KNUMV "单据条件数
B~EBELP "采购凭证的项目编号
B~MATNR "物料号
B~TXZ01 "短文本
INTO CORRESPONDING FIELDS OF TABLE GIT_EKPO
FROM EKKO AS A
INNER JOIN EKPO AS B
ON A~EBELN = B~EBELN
WHERE A~EBELN IN S_EBELN "采购凭证号
AND A~BUKRS IN S_BUKRS "公司代码
AND A~BSART IN S_BSART "采购凭证类型
AND B~MATNR IN S_MATNR. "物料号
IF GIT_EKPO[] IS NOT INITIAL.
SELECT
A~MBLNR "物料凭证编号
A~MJAHR "物料凭证年度
A~ZEILE "物料凭证中的项目
A~MENGE "数量
A~MEINS "基本计量单位
A~EBELN "采购凭证号
A~EBELP "采购凭证的项目编号
A~MATNR "物料号
A~WAERS "货币码
B~BUDAT "过账日期
INTO CORRESPONDING FIELDS OF TABLE GIT_MSEG
FROM MSEG AS A
INNER JOIN MKPF AS B
ON A~MBLNR = B~MBLNR
AND A~MJAHR = B~MJAHR
FOR ALL ENTRIES IN GIT_EKPO
WHERE A~MATNR IN S_MATNR
AND A~EBELN = GIT_EKPO-EBELN
AND A~EBELP = GIT_EKPO-EBELP
AND B~BUDAT IN S_BUDAT.
SELECT KNUMV "单据条件数
KPOSN "条件项目号
STUNR "步骤编号
ZAEHK "条件计数器
KBETR "基本计量单位
KSCHL "条件类型
INTO CORRESPONDING FIELDS OF TABLE GIT_KONV
FROM KONV
FOR ALL ENTRIES IN GIT_EKPO
WHERE KNUMV = GIT_EKPO-KNUMV
AND ( KSCHL = 'PB00' OR KSCHL = 'SKTO' ).
ENDIF.
ENDFORM. " FRM_GET_DATA
*&---------------------------------------------------------------------*
*& Form FRM_MERGE_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM FRM_MERGE_DATA .
SORT GIT_EKPO BY EBELN EBELP.
SORT GIT_KONV BY KNUMV KSCHL.
LOOP AT GIT_MSEG INTO GIT_MSEG.
GIT_RESULT-EBELN = GIT_MSEG-EBELN. "采购凭证
GIT_RESULT-EBELP = GIT_MSEG-EBELP. "采购凭证项目编号'
GIT_RESULT-MATNR = GIT_MSEG-MATNR. "物料号
GIT_RESULT-BUDAT = GIT_MSEG-BUDAT. "过账日期
GIT_RESULT-MENGE = GIT_MSEG-MENGE. "数量
GIT_RESULT-MEINS = GIT_MSEG-MEINS. "单位
GIT_RESULT-HUOBI = GIT_MSEG-WAERS. "货币
READ TABLE GIT_EKPO INTO GIT_EKPO
WITH KEY EBELN = GIT_RESULT-EBELN
EBELP = GIT_RESULT-EBELP
BINARY SEARCH.
IF SY-SUBRC = 0.
GIT_RESULT-TXZ01 = GIT_EKPO-TXZ01.
GIT_RESULT-BSART = GIT_EKPO-BSART.
READ TABLE GIT_KONV INTO GIT_KONV
WITH KEY KNUMV = GIT_EKPO-KNUMV
KSCHL = 'PB00'
BINARY SEARCH.
IF SY-SUBRC = 0.
GIT_RESULT-JIA01 = GIT_KONV-KBETR.
GIT_RESULT-JIN01 = GIT_RESULT-JIA01 * GIT_RESULT-MENGE.
CLEAR GIT_KONV.
ENDIF.
READ TABLE GIT_KONV INTO GIT_KONV
WITH KEY KNUMV = GIT_EKPO-KNUMV
KSCHL = 'SKTO'
BINARY SEARCH.
IF SY-SUBRC = 0.
GIT_RESULT-JIA02 = GIT_KONV-KBETR / 1000.
GIT_RESULT-JIN02 = GIT_RESULT-JIA01 / 100 * GIT_RESULT-JIN01.
CLEAR GIT_KONV.
ENDIF.
CLEAR GIT_EKPO.
ENDIF.
GIT_RESULT-JIN03 = GIT_RESULT-JIN01 GIT_RESULT-JIN02.
APPEND GIT_RESULT TO GIT_RESULT.
CLEAR GIT_RESULT.
CLEAR GIT_MSEG.
ENDLOOP.
ENDFORM. "FRM_MERGE_DATA
" FRM_MERGE_DATA
*&---------------------------------------------------------------------*
*& Form FRM_DISPLAY_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM FRM_DISPLAY_DATA .
DATA GS_FIELDCAT LIKE LINE OF GT_FIELDCAT.
DEFINE FC.
GS_FIELDCAT-FIELDNAME = &1.
IF NOT &2 IS INITIAL.
GS_FIELDCAT-REF_TABNAME = &2.
GS_FIELDCAT-REF_FIELDNAME = &3.
ELSE.
GS_FIELDCAT-OUTPUTLEN = &4.
GS_FIELDCAT-SELTEXT_L = &5.
ENDIF.
GS_FIELDCAT-LZERO = &6.
APPEND GS_FIELDCAT TO GT_FIELDCAT.
CLEAR GS_FIELDCAT.
END-OF-DEFINITION.
FC 'EBELN' '' '' '10' '采购凭证号.' ''.
FC 'EBELP' '' '' '6' '采购凭证的项目编号' ''.
FC 'BSART' '' '' '4' '采购凭证类型.' ''.
FC 'MATNR' '' '' '10' '物料号' ''.
FC 'TXZ01' '' '' '30' '短文本' ''.
FC 'BUDAT' '' '' '10' '凭证中的过帐日期' ''.
FC 'MENGE' '' '' '15' '数量' ''.
FC 'MEINS' '' '' '6' '单位' ''.
FC 'JIA01' '' '' '10' '净价' ''.
FC 'JIN01' '' '' '10' '金额' ''.
FC 'JIA02' '' '' '10' '折扣率' ''.
FC 'JIN02' '' '' '10' '折扣金额' ''.
FC 'JIN03' '' '' '10' '最终金额' ''.
FC 'HUOBI' '' '' '10' '货币' ''.
"调用显示
PRG = SY-REPID.
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
I_CALLBACK_PROGRAM = PRG
IT_FIELDCAT = GT_FIELDCAT
* is_layout = ls_layout
* i_save = 'A'
TABLES
T_OUTTAB = GIT_RESULT.
ENDFORM. " FRM_DISPLAY_DATA
