用Table Function 实现单一成本中心组查询下级所有层级的利润中心

     首先，我们在ADT中创建Table Funtion:ZTF_KOSTL_WITH_KHINR。如下所示：

@EndUserText.label: '单一成本中心组在下所有层级成本中心'
define table function ZTF_KOSTL_WITH_KHINR
with parameters
    @Environment.systemField: #CLIENT 
    iv_mandt : abap.clnt,
    iv_kokrs : kokrs,
    iv_khinr : khinr
returns {
  mandt : abap.clnt;
  kostl : kostl;
  khinr : khinr;
}
implemented by method zcl_amdp_tf=>get_kostl_with_khinr;

其次，在ADMP类:ZCL_ADMP_TF 中实现Table Funtion:ZTF_KOSTL_WITH_KHINR的功能，如下所示

CLASS zcl_amdp_tf DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.
    CLASS-METHODS:
      get_kostl_with_khinr FOR TABLE FUNCTION ztf_kostl_with_khinr.
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.



CLASS zcl_amdp_tf IMPLEMENTATION.


  METHOD get_kostl_with_khinr BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT
                              OPTIONS READ-ONLY USING setnode csks.

    declare lv_line int default 0;  -- 定义变量 行数

        if :iv_khinr = '' then
*        没有成本中心组取，默认取所有
            return select distinct
                          mandt,
                          kostl,
                          khinr
                        from csks 
                            where mandt = :iv_mandt
                              and kokrs = :iv_kokrs;
        else

            lt_subsetname = select iv_mandt as mandt,iv_khinr as setname from dummy;
            lt_setname_all = select * from :lt_subsetname;
            lv_line = 1;

            while :lv_line > 0 do
* 取出子集
                lt_subsetname = select mandt,
                                       subsetname as setname
                                   from setnode
                                      where setclass = '0101' -- 成本中心组 集类型
                                        and subclass = :iv_kokrs
                                        and ( mandt,setname )  in (
                                              select mandt,setname from :lt_subsetname
                                             );
* 获取子集的行数
                SELECT COUNT( * ) INTO lv_line FROM :lt_subsetname; --结果集的行数

* 结果集合并
                IF lv_line > 0 then
                    lt_setname_all = SELECT * FROM :lt_setname_all  -- 合并结果表
                                     UNION
                                     SELECT * FROM :lt_subsetname;
                END if;
            END while;


            RETURN SELECT mandt,
                          kostl,
                          khinr
                        from csks 
                            where ( mandt , khinr ) in ( 
                                    select mandt,setname as khinr from :lt_setname_all )
                               and kokrs = :iv_kokrs;
        end if;

  endmethod.

ENDCLASS.

最后，我们使用Table Funtion:ZTF_KOSTL_WITH_KHINR来实现查询需求，如下所示：

REPORT ztest_ztf_kostl_with_khinr.

PARAMETERS:p_khinr TYPE csks-khinr.

START-OF-SELECTION.

  SELECT b~kostl,
         b~khinr,
         b~FUNC_AREA
    FROM ztf_kostl_with_khinr( iv_kokrs = '8888', iv_khinr = @p_khinr ) as a
         inner join csks as b
      on a~kostl = b~kostl
     and b~kokrs = '8888'
      where b~FUNC_AREA = '2000'
    INTO TABLE @DATA(lt_result).
* 显示查询结果
  CL_DEMO_OUTPUT=>display(
    EXPORTING
      data = lt_result
  ).

运行测试程序，输入条件如下图

查询结果如下图