【SAP Abap】SAP S/4 ABAP OPEN SQL中WITH的应用案例

X档案库

已于 2022-07-26 13:30:06 修改

阅读量1.8k

点赞数 1

分类专栏： SAP Hana SAP Abap 文章标签： sql 数据库

于 2022-07-26 10:30:31 首次发布

本文链接：https://blog.csdn.net/XLevon/article/details/125989172

版权

SAP Abap 同时被 2 个专栏收录

40 篇文章 86 订阅

订阅专栏

SAP Hana

24 篇文章 64 订阅

订阅专栏

SAP S/4 ABAP OPEN SQL中WITH的应用案例

1、原代码
2、调整后代码
3、对比

1、原代码

以HD实施开发的报表《往来交易与余额表》代码为例，原代码如下：

REPORT zfir_028.
TYPE-POOLS:slis.
TABLES:t001w,skb1,acdoca,kna1,lfa1.

TYPES:BEGIN OF ty_alv,
        racct   TYPE acdoca-racct,               "科目
        txt20   TYPE skat-txt20,                 "科目描述
        kunnr   TYPE acdoca-kunnr,               "客户编码
        name1_k TYPE kna1-name1,                 "客户名称
        lifnr   TYPE acdoca-lifnr,               "供应商
        name1_l TYPE  lfa1-name1,                "供应商编码
        rwcur   TYPE acdoca-rwcur,               "币别
        wsl_q   TYPE acdoca-wsl,                 "期初余额-原币
        hsl_q   TYPE acdoca-hsl,                 "期初余额-本位币
        wsl_s   TYPE acdoca-wsl,                 "借方发生额-原币
        hsl_s   TYPE acdoca-hsl,                 "借方发生额-本位币
        wsl_h   TYPE acdoca-wsl,                 "贷方发生额-原币
        hsl_h   TYPE acdoca-hsl,                 "贷方发生额-本位币
        wsl_e   TYPE acdoca-wsl,                 "期末余额-原币
        hsl_e   TYPE acdoca-hsl,                 "期末余额-本位币
      END OF ty_alv.

TYPES: BEGIN OF ty_acdoca,
         rbukrs TYPE acdoca-rbukrs,               "公司代码
         gjahr  TYPE acdoca-gjahr,                "会计凭证号
         belnr  TYPE acdoca-belnr,                "会计年度
         docln  TYPE acdoca-docln,                "凭证行项目
*         POPER  TYPE ACDOCA-POPER,               "期间
         kunnr  TYPE acdoca-kunnr,                "客户
         lifnr  TYPE acdoca-lifnr ,               "供应商
         rwcur  TYPE acdoca-rwcur,                "币别
*         AUGDT  TYPE ACDOCA-AUGDT,               "清算日期
         drcrk  TYPE acdoca-drcrk,                "借贷项标识
         racct  TYPE acdoca-racct,                "科目
         wsl    TYPE acdoca-wsl,                  "凭证金额
         hsl    TYPE acdoca-hsl,                  "本位币金额
       END OF ty_acdoca.
TYPES: BEGIN OF ty_kna1,
         kunnr   TYPE kna1-kunnr,
         name1_k TYPE kna1-name1,
       END OF ty_kna1.

TYPES: BEGIN OF ty_lfa1,
         lifnr   TYPE lfa1-lifnr,
         name1_l TYPE lfa1-name1,
       END OF ty_lfa1,

       BEGIN OF ty_skat,
         saknr TYPE skat-saknr,
         txt20 TYPE skat-txt20,
       END OF ty_skat.

DATA:
  t_acdoca1c TYPE STANDARD TABLE OF ty_acdoca,
  w_acdoca1c TYPE ty_acdoca,
  t_acdoca2c TYPE STANDARD TABLE OF ty_acdoca,
  w_acdoca2c TYPE ty_acdoca,
  t_acdoca3c TYPE STANDARD TABLE OF ty_acdoca,
  w_acdoca3c TYPE ty_acdoca,
  t_acdoca   TYPE STANDARD TABLE OF ty_acdoca,
  w_acdoca   TYPE ty_acdoca,
  t_acdoca1  TYPE STANDARD TABLE OF ty_acdoca,
  w_acdoca1  TYPE ty_acdoca,
  t_acdoca2  TYPE STANDARD TABLE OF ty_acdoca,
  w_acdoca2  TYPE ty_acdoca,
  t_acdoca3  TYPE STANDARD TABLE OF ty_acdoca,
  w_acdoca3  TYPE ty_acdoca,
  t_lfa1     TYPE STANDARD TABLE OF ty_lfa1,
  w_lfa1     TYPE ty_lfa1,
  t_kna1     TYPE STANDARD TABLE OF ty_kna1,
  w_kna1     TYPE ty_kna1,
  t_alv      TYPE STANDARD TABLE OF ty_alv,
  w_alv      TYPE ty_alv,
  t_skat     TYPE STANDARD TABLE OF ty_skat,
  w_skat     TYPE ty_skat,
  t_fieldcat TYPE slis_t_fieldcat_alv,
  w_fieldcat TYPE slis_fieldcat_alv,
  w_layout   TYPE slis_layout_alv.

SELECTION-SCREEN BEGIN OF BLOCK blk WITH FRAME TITLE TEXT-001.
  PARAMETERS: p_bukrs TYPE t001-bukrs  OBLIGATORY DEFAULT '1000',
              p_gjahr TYPE acdoca-gjahr OBLIGATORY DEFAULT sy-datum+0(4).
  SELECT-OPTIONS: s_poper FOR acdoca-poper OBLIGATORY DEFAULT sy-datum+4(2),
                  s_racct FOR skb1-saknr OBLIGATORY,
                  s_kunnr FOR kna1-kunnr ,
                  s_lifnr FOR lfa1-lifnr .
SELECTION-SCREEN END OF BLOCK blk.

AT SELECTION-SCREEN ON p_bukrs.
  PERFORM check_purview.  "检查公司代码

START-OF-SELECTION.
* 读取数据
  PERFORM frm_get_data.
* 处理数据
  PERFORM frm_handle_data.
* 报表显示
  PERFORM frm_show_data.
*&---------------------------------------------------------------------*
* END-OF-SELECTION
*&---------------------------------------------------------------------*
END-OF-SELECTION.

*----------------------------------------------------------------------*
FORM check_purview .
*******************权限检查
  AUTHORITY-CHECK OBJECT 'F_BKPF_BUK'
                     ID 'BUKRS' FIELD p_bukrs
                     ID 'ACTVT' FIELD '03'.

  IF sy-subrc <> 0.
    MESSAGE '没有该公司代码的权限！' TYPE 'E' .
  ENDIF.

ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  FRM_GET_DATA
*&---------------------------------------------------------------------*
* 读取数据
*----------------------------------------------------------------------*

FORM frm_get_data .

  DATA: lv_start TYPE sy-datum.

  CONCATENATE p_gjahr s_poper-low+1(2) '01' INTO lv_start.

  IF s_poper-low  = '01'.
*****查询期初数据*******
    SELECT  rbukrs
          gjahr
          belnr
          docln
          racct
          kunnr
          lifnr
          rwcur
          drcrk
          wsl
          hsl
    INTO CORRESPONDING FIELDS OF TABLE t_acdoca1
    FROM acdoca
    WHERE rbukrs = p_bukrs
    AND   ( gjahr < p_gjahr )
    AND   racct IN s_racct
    AND   bttype <> 'RFBC'
    AND   blart  <> 'AB' 
    AND   kunnr IN s_kunnr
    AND   lifnr IN s_lifnr
    AND   ( augdt = '00000000' OR augdt >= lv_start ).

  ELSE.
    SELECT  rbukrs
            gjahr
            belnr
            docln
            racct
            kunnr
            lifnr
            rwcur
            drcrk
            wsl
            hsl
      INTO CORRESPONDING FIELDS OF TABLE t_acdoca1
      FROM acdoca
      WHERE rbukrs = p_bukrs
      AND   ( gjahr < p_gjahr OR ( gjahr = p_gjahr  AND   poper < s_poper-low ) )
      AND   bttype <> 'RFBC'
      AND   blart  <> 'AB' 
      AND   racct IN s_racct
      AND   kunnr IN s_kunnr
      AND   lifnr IN s_lifnr
      AND   ( augdt = '00000000' OR augdt >= lv_start ).
  ENDIF.
*******查询借方数据*******

  SELECT rbukrs
         gjahr
         belnr
         docln
         racct
         kunnr
         lifnr
         rwcur
         drcrk
         wsl
         hsl
  INTO CORRESPONDING FIELDS OF TABLE t_acdoca2
  FROM acdoca
  WHERE rbukrs =  p_bukrs
    AND gjahr = p_gjahr
    AND blart  <> 'AB'
    AND racct IN s_racct
    AND kunnr IN s_kunnr
    AND lifnr IN s_lifnr
    AND poper IN s_poper
    AND drcrk = 'S'.

******查询贷方数据********
  SELECT rbukrs
        gjahr
        belnr
        docln
        racct
        kunnr
        lifnr
        rwcur
        drcrk
        wsl
        hsl
 INTO CORRESPONDING FIELDS OF TABLE t_acdoca3
 FROM acdoca
 WHERE rbukrs =  p_bukrs
   AND gjahr = p_gjahr
   AND blart  <> 'AB'
   AND racct IN s_racct
   AND kunnr IN s_kunnr
   AND lifnr IN s_lifnr
   AND poper IN s_poper
   AND drcrk = 'H'.


********期初按照客户&供应商&币别&科目合计*****

  LOOP AT t_acdoca1 INTO w_acdoca1.
    w_acdoca1c-kunnr = w_acdoca1-kunnr.
    w_acdoca1c-lifnr = w_acdoca1-lifnr.
    w_acdoca1c-rwcur = w_acdoca1-rwcur.
    w_acdoca1c-racct = w_acdoca1-racct.
    w_acdoca1c-wsl = w_acdoca1-wsl.
    w_acdoca1c-hsl = w_acdoca1-hsl.
    COLLECT w_acdoca1c INTO t_acdoca1c.
  ENDLOOP.

  SORT t_acdoca1c BY racct kunnr lifnr rwcur .

*******借方发生合计*******
  LOOP AT t_acdoca2 INTO w_acdoca2.
    w_acdoca2c-kunnr = w_acdoca2-kunnr.
    w_acdoca2c-lifnr = w_acdoca2-lifnr.
    w_acdoca2c-rwcur = w_acdoca2-rwcur.
    w_acdoca2c-racct = w_acdoca2-racct.
    w_acdoca2c-wsl = w_acdoca2-wsl.
    w_acdoca2c-hsl = w_acdoca2-hsl.
    COLLECT w_acdoca2c INTO t_acdoca2c.
  ENDLOOP.

  SORT t_acdoca2c BY racct kunnr lifnr rwcur .
*******贷方发生合计*******
  LOOP AT t_acdoca3 INTO w_acdoca3.
    w_acdoca3c-kunnr = w_acdoca3-kunnr.
    w_acdoca3c-lifnr = w_acdoca3-lifnr.
    w_acdoca3c-rwcur = w_acdoca3-rwcur.
    w_acdoca3c-racct = w_acdoca3-racct.
    w_acdoca3c-wsl = 0 - w_acdoca3-wsl.
    w_acdoca3c-hsl = 0 - w_acdoca3-hsl.
    COLLECT w_acdoca3c INTO t_acdoca3c.
  ENDLOOP.

  SORT t_acdoca3c BY racct kunnr lifnr rwcur .
*******合并所有的客户与供应商清单+币别*科目*****
  LOOP AT t_acdoca1c INTO w_acdoca1c.
    w_acdoca-kunnr = w_acdoca1c-kunnr.
    w_acdoca-lifnr = w_acdoca1c-lifnr.
    w_acdoca-racct = w_acdoca1c-racct.
    w_acdoca-rwcur = w_acdoca1c-rwcur.
    APPEND w_acdoca TO t_acdoca.
    CLEAR w_acdoca.
  ENDLOOP.
  LOOP AT t_acdoca2c INTO w_acdoca2c.
    w_acdoca-kunnr = w_acdoca2c-kunnr.
    w_acdoca-lifnr = w_acdoca2c-lifnr.
    w_acdoca-racct = w_acdoca2c-racct.
    w_acdoca-rwcur = w_acdoca2c-rwcur.
    APPEND w_acdoca TO t_acdoca.
    CLEAR w_acdoca.
  ENDLOOP.
  LOOP AT t_acdoca3c INTO w_acdoca3c.
    w_acdoca-kunnr = w_acdoca3c-kunnr.
    w_acdoca-lifnr = w_acdoca3c-lifnr.
    w_acdoca-racct = w_acdoca3c-racct.
    w_acdoca-rwcur = w_acdoca3c-rwcur.
    APPEND w_acdoca TO t_acdoca.
    CLEAR w_acdoca.
  ENDLOOP.

  SORT t_acdoca BY racct kunnr lifnr rwcur.

  DELETE ADJACENT DUPLICATES FROM t_acdoca COMPARING ALL FIELDS.

********读取科目描述、客户描述、供应商描述*&***********
  IF t_acdoca[] IS NOT INITIAL.
    SELECT
      saknr
      txt20
    INTO CORRESPONDING FIELDS OF TABLE t_skat
    FROM skat
    FOR ALL ENTRIES IN t_acdoca
    WHERE saknr = t_acdoca-racct
      AND ktopl = '1000'
      AND spras = '1'.

    SORT t_skat BY saknr.

    SELECT
      kunnr
      name1 AS name1_k
    INTO CORRESPONDING FIELDS OF TABLE t_kna1
    FROM kna1
    FOR ALL ENTRIES IN t_acdoca
    WHERE kunnr = t_acdoca-kunnr.

    SORT t_kna1 BY kunnr.

    SELECT
      lifnr
      name1 AS name1_l
    INTO CORRESPONDING FIELDS OF TABLE t_lfa1
    FROM lfa1
    FOR ALL ENTRIES IN t_acdoca
    WHERE lifnr = t_acdoca-lifnr.

    SORT t_lfa1 BY lifnr.
  ENDIF.
**********
ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  FRM_HANDLE_DATA
*&---------------------------------------------------------------------*
*      处理数据
*----------------------------------------------------------------------*
FORM frm_handle_data.

*****循环读数赋值*******
  LOOP AT t_acdoca INTO w_acdoca.
    w_alv-racct = w_acdoca-racct.
    w_alv-kunnr = w_acdoca-kunnr.
    w_alv-lifnr = w_acdoca-lifnr.
    w_alv-rwcur = w_acdoca-rwcur.
    READ TABLE t_acdoca1c INTO w_acdoca1c WITH KEY racct  =  w_acdoca-racct kunnr = w_acdoca-kunnr lifnr = w_acdoca-lifnr rwcur = w_acdoca-rwcur BINARY SEARCH.
    IF sy-subrc = 0.
      w_alv-wsl_q = w_acdoca1c-wsl.
      w_alv-hsl_q = w_acdoca1c-hsl.
    ENDIF.
    READ TABLE t_acdoca2c INTO w_acdoca2c WITH KEY racct  =  w_acdoca-racct kunnr = w_acdoca-kunnr lifnr = w_acdoca-lifnr rwcur = w_acdoca-rwcur BINARY SEARCH.
    IF sy-subrc = 0.
      w_alv-wsl_s = w_acdoca2c-wsl.
      w_alv-hsl_s = w_acdoca2c-hsl.
    ENDIF.
    READ TABLE t_acdoca3c INTO w_acdoca3c WITH KEY racct  =  w_acdoca-racct kunnr = w_acdoca-kunnr lifnr = w_acdoca-lifnr rwcur = w_acdoca-rwcur BINARY SEARCH.
    IF sy-subrc = 0.
      w_alv-wsl_h = w_acdoca3c-wsl.
      w_alv-hsl_h = w_acdoca3c-hsl.
    ENDIF.
    w_alv-wsl_e = w_alv-wsl_q + w_alv-wsl_s - w_alv-wsl_h.
    w_alv-hsl_e = w_alv-hsl_q + w_alv-hsl_s - w_alv-hsl_h.
*    IF W_ALV-RACCT >='2000000000'.
*      W_ALV-WSL_Q = 0 - W_ALV-WSL_Q .
*      W_ALV-HSL_Q = 0 - W_ALV-HSL_Q .
*      W_ALV-WSL_S = 0 - W_ALV-WSL_S .
*      W_ALV-HSL_S = 0 - W_ALV-HSL_S .
    w_alv-wsl_h = 0 - w_alv-wsl_h .
    w_alv-hsl_h = 0 - w_alv-hsl_h .
*      W_ALV-WSL_E = 0 - W_ALV-WSL_E .
*      W_ALV-HSL_E = 0 - W_ALV-HSL_E .
*    ENDIF.
    READ TABLE t_skat INTO w_skat WITH KEY saknr = w_acdoca-racct BINARY SEARCH.
    IF sy-subrc = 0.
      w_alv-txt20 = w_skat-txt20.
    ENDIF.
    READ  TABLE t_kna1 INTO w_kna1 WITH KEY kunnr = w_acdoca-kunnr BINARY SEARCH.
    IF sy-subrc = 0.
      w_alv-name1_k = w_kna1-name1_k.
    ENDIF.
    READ TABLE t_lfa1 INTO w_lfa1 WITH KEY lifnr = w_acdoca-lifnr BINARY SEARCH.
    IF sy-subrc = 0.
      w_alv-name1_l = w_lfa1-name1_l.
    ENDIF.
    PERFORM conversion_exit_alpha_output CHANGING w_alv-kunnr.
    PERFORM conversion_exit_alpha_output CHANGING w_alv-lifnr.
    APPEND w_alv TO t_alv.
    CLEAR w_alv.
  ENDLOOP.
  DELETE t_alv WHERE  wsl_q = '0.00' AND hsl_q = '0.00' AND wsl_s = '0.00' AND hsl_s = '0.00' AND wsl_h = '0.00' AND hsl_h = '0.00' AND wsl_e = '0.00' AND hsl_e = '0.00'.
  SORT t_alv BY racct kunnr lifnr rwcur.


ENDFORM.                    " FRM_HANDLE_DATA
*&---------------------------------------------------------------------*
*&      Form  FRM_SHOW_DATA
*&---------------------------------------------------------------------*
*       显示数据
*----------------------------------------------------------------------*

FORM frm_show_data .

* 设置显示目录
  PERFORM frm_fieldcat USING:
*--------------------------------------------------------------------*
* 关键字  复选框  编辑  单击  对齐  字段名  字段描述
*--------------------------------------------------------------------*
   ' '     ' '    ' '    ' '  'R'   'RACCT'   '总账科目',
   ' '     ' '    ' '    ' '  'L'   'TXT20'   '科目描述',
   ' '     ' '    ' '    ' '  'R'   'KUNNR'   '客户编号',
   ' '     ' '    ' '    ' '  'L'   'NAME1_K'   '客户描述',
   ' '     ' '    ' '    ' '  'R'   'LIFNR'   '供应商编码',
   ' '     ' '    ' '    ' '  'R'   'NAME1_L'   '供应商描述',
   ' '     ' '    ' '    ' '  'R'   'RWCUR'   '币别',
   ' '     ' '    ' '    ' '  'R'   'WSL_Q'   '期初原币',
   ' '     ' '    ' '    ' '  'R'   'HSL_Q'   '期初本币',
   ' '     ' '    ' '    ' '  'R'   'WSL_S'   '期间借方发生额-原币',
   ' '     ' '    ' '    ' '  'R'   'HSL_S'   '期间借方发生额-本位币',
   ' '     ' '    ' '    ' '  'R'   'WSL_H'   '期间贷方发生额-原币',
   ' '     ' '    ' '    ' '  'R'   'HSL_H'   '期间贷方发生额-本位币',
   ' '     ' '    ' '    ' '  'R'   'WSL_E'   '期末原币',
   ' '     ' '    ' '    ' '  'R'   'HSL_E'   '期末本位币'
   .

* 设置显示格式
  PERFORM frm_layout.
* ALV显示
  PERFORM frm_alv_show.
ENDFORM.                    " FRM_SHOW_DATA
*&---------------------------------------------------------------------*
*&      Form  FRM_FIELDCAT
*&---------------------------------------------------------------------*
*       设置目录
*----------------------------------------------------------------------*
FORM frm_fieldcat  USING
                         fu_key
                         fu_checkbox
                         fu_edit
                         fu_hotspot
                         fu_just
                         fu_fieldname
                         fu_seltext.
  CLEAR w_fieldcat.

  w_fieldcat-key           = fu_key.
  w_fieldcat-checkbox      = fu_checkbox.
  w_fieldcat-edit          = fu_edit.
  w_fieldcat-hotspot       = fu_hotspot.
  w_fieldcat-just          = fu_just.
  w_fieldcat-fieldname     = fu_fieldname.
  w_fieldcat-seltext_s     = fu_seltext.
  w_fieldcat-seltext_m     = fu_seltext.
  w_fieldcat-seltext_l     = fu_seltext.

  APPEND w_fieldcat TO t_fieldcat.
ENDFORM.                    "frm_fieldcat
*&---------------------------------------------------------------------*
*&      Form  FRM_LAYOUT
*&---------------------------------------------------------------------*
*      设置数据格式
*----------------------------------------------------------------------*

FORM frm_layout .
  w_layout-zebra = 'X'.
  w_layout-colwidth_optimize = 'X'.
ENDFORM.                    " FRM_LAYOUT
*&---------------------------------------------------------------------*
*&      Form  FRM_ALV_SHOW
*&---------------------------------------------------------------------*
*       alv显示
*----------------------------------------------------------------------*

FORM frm_alv_show .
  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
      i_callback_program       = sy-repid
      i_callback_pf_status_set = 'FRM_PF_STATUS'
      is_layout                = w_layout
      it_fieldcat              = t_fieldcat
      i_default                = 'X'
      i_save                   = 'A'
    TABLES
      t_outtab                 = t_alv
    EXCEPTIONS
      program_error            = 1
      OTHERS                   = 2.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.
ENDFORM.                    " FRM_ALV_SHOW
*&---------------------------------------------------------------------*
*&      Form  PF_STATUS
*&---------------------------------------------------------------------*
*       设置工具栏
*----------------------------------------------------------------------*
FORM frm_pf_status USING fu_extab TYPE slis_t_extab.
  SET PF-STATUS 'STANDARD' EXCLUDING fu_extab.
ENDFORM.                    "frm_pf_status
*&---------------------------------------------------------------------*
*&      Form  CONVERSION_EXIT_ALPHA_OUTPUT
*&---------------------------------------------------------------------*
*       前置零转出
*----------------------------------------------------------------------*
FORM conversion_exit_alpha_output CHANGING fc_alpha.
  CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
    EXPORTING
      input  = fc_alpha
    IMPORTING
      output = fc_alpha.
ENDFORM.                " CONVERSION_EXIT_ALPHA_OUTPut
*&---------------------------------------------------------------------*
*&      Form  CONVERSION_EXIT_CUNIT_OUTPUT
*&---------------------------------------------------------------------*
*       内外部单位转换
*----------------------------------------------------------------------*
FORM conversion_exit_cunit_output CHANGING fc_cunit.
  CALL FUNCTION 'CONVERSION_EXIT_CUNIT_OUTPUT'
    EXPORTING
      input    = fc_cunit
      language = sy-langu
    IMPORTING
      output   = fc_cunit.
ENDFORM.                " CONVERSION_EXIT_CUINT_OUTPut

2、调整后代码

保持逻辑不变，主要对其中的两个FORM（frm_get_data和frm_handle_data）进行改造，合并为一个FORM（frm_get_data），调整后代码如下：

REPORT zfir_028n.
TYPE-POOLS:slis.
TABLES:skb1,acdoca,kna1,lfa1.

TYPES:BEGIN OF ty_alv,
        racct   TYPE acdoca-racct,               "科目
        txt20   TYPE skat-txt20,                 "科目描述
        kunnr   TYPE acdoca-kunnr,               "客户编码
        name1_k TYPE kna1-name1,                 "客户名称
        lifnr   TYPE acdoca-lifnr,               "供应商
        name1_l TYPE  lfa1-name1,                "供应商编码
        rwcur   TYPE acdoca-rwcur,               "币别
        wsl_q   TYPE acdoca-wsl,                 "期初余额-原币
        hsl_q   TYPE acdoca-hsl,                 "期初余额-本位币
        wsl_s   TYPE acdoca-wsl,                 "借方发生额-原币
        hsl_s   TYPE acdoca-hsl,                 "借方发生额-本位币
        wsl_h   TYPE acdoca-wsl,                 "贷方发生额-原币
        hsl_h   TYPE acdoca-hsl,                 "贷方发生额-本位币
        wsl_e   TYPE acdoca-wsl,                 "期末余额-原币
        hsl_e   TYPE acdoca-hsl,                 "期末余额-本位币
      END OF ty_alv.

DATA:
  t_alv      TYPE STANDARD TABLE OF ty_alv,
  t_fieldcat TYPE slis_t_fieldcat_alv,
  w_fieldcat TYPE slis_fieldcat_alv,
  w_layout   TYPE slis_layout_alv.

SELECTION-SCREEN BEGIN OF BLOCK blk WITH FRAME TITLE TEXT-001.
  PARAMETERS: p_bukrs TYPE t001-bukrs  OBLIGATORY DEFAULT '1000',
              p_gjahr TYPE acdoca-gjahr OBLIGATORY DEFAULT sy-datum+0(4).
  SELECT-OPTIONS: s_poper FOR acdoca-poper OBLIGATORY DEFAULT sy-datum+4(2),
                  s_racct FOR skb1-saknr OBLIGATORY,
                  s_kunnr FOR kna1-kunnr ,
                  s_lifnr FOR lfa1-lifnr .
SELECTION-SCREEN END OF BLOCK blk.

AT SELECTION-SCREEN ON p_bukrs.
  PERFORM check_purview.  "检查公司代码

START-OF-SELECTION.
* 读取数据
  PERFORM frm_get_data.
* 报表显示
  PERFORM frm_show_data.

END-OF-SELECTION.

*----------------------------------------------------------------------*
FORM check_purview .
*******************权限检查
  AUTHORITY-CHECK OBJECT 'F_BKPF_BUK'
                     ID 'BUKRS' FIELD p_bukrs
                     ID 'ACTVT' FIELD '03'.

  IF sy-subrc <> 0.
    MESSAGE '没有该公司代码的权限！' TYPE 'E' .
  ENDIF.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  FRM_GET_DATA
*&---------------------------------------------------------------------*
* 读取数据
*----------------------------------------------------------------------*
FORM frm_get_data .

  DATA: lv_start TYPE sy-datum.
  CONCATENATE p_gjahr s_poper-low+1(2) '01' INTO lv_start.

   WITH
   "获取期初数据
   +acdoca_qc AS (
    SELECT
      racct, kunnr, lifnr, rwcur, SUM( wsl ) AS wsl, SUM( hsl ) AS hsl
    FROM acdoca
    WHERE rbukrs = @p_bukrs
      AND ( gjahr < @p_gjahr OR ( gjahr = @p_gjahr AND poper < @s_poper-low ) )
      AND racct IN @s_racct
      AND kunnr IN @s_kunnr
      AND lifnr IN @s_lifnr
      AND bttype <> 'RFBC'      "余额结转
      "AND blart  <> 'AB'       "调整仅本期剔除AB凭证类型，期初保留AB类型
      AND ( augdt = '00000000' OR augdt >= @lv_start )
      "AND RCLNT in ( 400, 710, 800 )
    GROUP BY racct, kunnr, lifnr, rwcur ),
    "获取本期数据，通过 drcrk 区分借方 S、贷方 H
    +acdoca_bq AS (
    SELECT
      racct, kunnr, lifnr, rwcur, SUM( wsl ) AS wsl, SUM( hsl ) AS hsl, drcrk
    FROM acdoca
    WHERE rbukrs = @p_bukrs
      AND gjahr = @p_gjahr
      AND poper IN @s_poper
      AND racct IN @s_racct
      AND kunnr IN @s_kunnr
      AND lifnr IN @s_lifnr
      AND bttype <> 'RFBC'
      AND blart  <> 'AB'
      AND drcrk IN ( 'S', 'H' )     "借方 S、贷方 H
      "AND RCLNT in ( 400, 710, 800 )
    GROUP BY racct, kunnr, lifnr, rwcur, drcrk ),
    "获取公共数据项
    +header_list AS (
      SELECT t1~racct, t1~kunnr, t1~lifnr, t1~rwcur FROM +acdoca_qc AS t1
      UNION
      SELECT t2~racct, t2~kunnr, t2~lifnr, t2~rwcur FROM +acdoca_bq AS t2
    ),
    "计算数据
    +data_list AS (
    SELECT h~racct, h~kunnr, h~lifnr, h~rwcur,
      coalesce( qc~wsl,0 ) AS wsl_q, coalesce( qc~hsl,0 ) AS hsl_q,
      coalesce( bq_jf~wsl,0 ) AS wsl_s, coalesce( bq_jf~hsl,0 ) AS hsl_S,
      coalesce( bq_df~wsl,0 ) AS wsl_h, coalesce( bq_df~hsl,0 ) AS hsl_H,
      coalesce( qc~wsl,0 ) + coalesce( bq_jf~wsl,0 ) + coalesce( bq_df~wsl,0 ) AS wsl_E,
      coalesce( qc~hsl,0 ) + coalesce( bq_jf~hsl,0 ) + coalesce( bq_df~hsl,0 ) AS hsl_E
    FROM +header_list AS h
    LEFT JOIN +acdoca_qc AS qc ON qc~racct = h~racct AND qc~kunnr = h~kunnr
      AND qc~lifnr = h~lifnr AND qc~rwcur = h~rwcur
    LEFT JOIN +acdoca_bq AS bq_jf ON bq_jf~racct = h~racct AND bq_jf~kunnr = h~kunnr
      AND bq_jf~lifnr = h~lifnr AND bq_jf~rwcur = h~rwcur AND bq_jf~drcrk = 'S'
    LEFT JOIN +acdoca_bq AS bq_df ON bq_df~racct = h~racct AND bq_df~kunnr = h~kunnr
      AND bq_df~lifnr = h~lifnr AND bq_df~rwcur = h~rwcur AND bq_df~drcrk = 'H' )
   "输出数据
   SELECT a~racct, skat~txt20, a~kunnr, kna1~name1 AS name1_k, a~lifnr, lfa1~name1 AS name1_l, a~rwcur,
   	  wsl_q, hsl_q, wsl_s, hsl_S, wsl_h, hsl_H, wsl_E, hsl_E
   FROM +data_list AS a
   LEFT JOIN skat ON skat~spras = '1' AND skat~ktopl = '1000' AND skat~saknr = a~racct "AND skat~mandt in ( 400, 710, 800 )
   LEFT JOIN kna1 ON kna1~kunnr = a~kunnr "AND kna1~mandt in ( 400, 710, 800 )
   LEFT JOIN lfa1 ON lfa1~lifnr = a~lifnr "AND lfa1~mandt in ( 400, 710, 800 )
   WHERE ( abs( wsl_q ) + abs( wsl_s ) + abs( wsl_h ) ) > 0
   INTO TABLE @t_alv.

ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  FRM_SHOW_DATA
*&---------------------------------------------------------------------*
*       显示数据
*----------------------------------------------------------------------*

FORM frm_show_data .

* 设置显示目录
  PERFORM frm_fieldcat USING:
*--------------------------------------------------------------------*
* 关键字  复选框  编辑  单击  对齐  字段名  字段描述
*--------------------------------------------------------------------*
   ' '     ' '    ' '    ' '  'R'   'RACCT'   '总账科目',
   ' '     ' '    ' '    ' '  'L'   'TXT20'   '科目描述',
   ' '     ' '    ' '    ' '  'R'   'KUNNR'   '客户编号',
   ' '     ' '    ' '    ' '  'L'   'NAME1_K'   '客户描述',
   ' '     ' '    ' '    ' '  'R'   'LIFNR'   '供应商编码',
   ' '     ' '    ' '    ' '  'R'   'NAME1_L'   '供应商描述',
   ' '     ' '    ' '    ' '  'R'   'RWCUR'   '币别',
   ' '     ' '    ' '    ' '  'R'   'WSL_Q'   '期初原币',
   ' '     ' '    ' '    ' '  'R'   'HSL_Q'   '期初本币',
   ' '     ' '    ' '    ' '  'R'   'WSL_S'   '期间借方发生额-原币',
   ' '     ' '    ' '    ' '  'R'   'HSL_S'   '期间借方发生额-本位币',
   ' '     ' '    ' '    ' '  'R'   'WSL_H'   '期间贷方发生额-原币',
   ' '     ' '    ' '    ' '  'R'   'HSL_H'   '期间贷方发生额-本位币',
   ' '     ' '    ' '    ' '  'R'   'WSL_E'   '期末原币',
   ' '     ' '    ' '    ' '  'R'   'HSL_E'   '期末本位币'
   .

* 设置显示格式
  PERFORM frm_layout.
* ALV显示
  PERFORM frm_alv_show.
ENDFORM.                    " FRM_SHOW_DATA

*&---------------------------------------------------------------------*
*&      Form  FRM_FIELDCAT
*&---------------------------------------------------------------------*
*       设置目录
*----------------------------------------------------------------------*
FORM frm_fieldcat  USING
                         fu_key
                         fu_checkbox
                         fu_edit
                         fu_hotspot
                         fu_just
                         fu_fieldname
                         fu_seltext.
  CLEAR w_fieldcat.

  w_fieldcat-key           = fu_key.
  w_fieldcat-checkbox      = fu_checkbox.
  w_fieldcat-edit          = fu_edit.
  w_fieldcat-hotspot       = fu_hotspot.
  w_fieldcat-just          = fu_just.
  w_fieldcat-fieldname     = fu_fieldname.
  w_fieldcat-seltext_s     = fu_seltext.
  w_fieldcat-seltext_m     = fu_seltext.
  w_fieldcat-seltext_l     = fu_seltext.

  APPEND w_fieldcat TO t_fieldcat.
ENDFORM.                    "frm_fieldcat
*&---------------------------------------------------------------------*
*&      Form  FRM_LAYOUT
*&---------------------------------------------------------------------*
*      设置数据格式
*----------------------------------------------------------------------*

FORM frm_layout .
  w_layout-zebra = 'X'.
  w_layout-colwidth_optimize = 'X'.
ENDFORM.                    " FRM_LAYOUT
*&---------------------------------------------------------------------*
*&      Form  FRM_ALV_SHOW
*&---------------------------------------------------------------------*
*       alv显示
*----------------------------------------------------------------------*

FORM frm_alv_show .
  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
      i_callback_program       = sy-repid
      i_callback_pf_status_set = 'FRM_PF_STATUS'
      is_layout                = w_layout
      it_fieldcat              = t_fieldcat
      i_default                = 'X'
      i_save                   = 'A'
    TABLES
      t_outtab                 = t_alv
    EXCEPTIONS
      program_error            = 1
      OTHERS                   = 2.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.
ENDFORM.                    " FRM_ALV_SHOW

*&---------------------------------------------------------------------*
*&      Form  PF_STATUS
*&---------------------------------------------------------------------*
*       设置工具栏
*----------------------------------------------------------------------*
FORM frm_pf_status USING fu_extab TYPE slis_t_extab.
  SET PF-STATUS 'STANDARD' EXCLUDING fu_extab.
ENDFORM.                    "frm_pf_status

*&---------------------------------------------------------------------*
*&      Form  CONVERSION_EXIT_ALPHA_OUTPUT
*&---------------------------------------------------------------------*
*       前置零转出
*----------------------------------------------------------------------*
FORM conversion_exit_alpha_output CHANGING fc_alpha.
  CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
    EXPORTING
      input  = fc_alpha
    IMPORTING
      output = fc_alpha.
ENDFORM.                " CONVERSION_EXIT_ALPHA_OUTPut

*&---------------------------------------------------------------------*
*&      Form  CONVERSION_EXIT_CUNIT_OUTPUT
*&---------------------------------------------------------------------*
*       内外部单位转换
*----------------------------------------------------------------------*
FORM conversion_exit_cunit_output CHANGING fc_cunit.
  CALL FUNCTION 'CONVERSION_EXIT_CUNIT_OUTPUT'
    EXPORTING
      input    = fc_cunit
      language = sy-langu
    IMPORTING
      output   = fc_cunit.
ENDFORM.                " CONVERSION_EXIT_CUINT_OUTPut

3、对比

（1）原方法：
优点：逻辑分层，比较容易理解；代码可调试
缺点：代码冗长，多次访问数据库，多次循环操作，数据量大的时候代码效率低
（2）新方法：
优点：代码精简，一次访问数据库，减少循环操作，充分利用hana内存计算性能，能保证数据量大的时候代码效率
缺点：代码不好调试，需借助hana studio查询验证脚本