SAP ABAP 有关配额和预测的 CDS 改写

gavin_gxh

已于 2024-11-05 11:17:07 修改

阅读量205

点赞数 1

分类专栏： ABAP HANA 文章标签：服务器数据库

于 2024-11-05 11:15:37 首次发布

gavin-gxh

本文链接：https://blog.csdn.net/xiaofei2008gxh/article/details/143503846

版权

ABAP 同时被 2 个专栏收录

290 篇文章 20 订阅

订阅专栏

HANA

4 篇文章 0 订阅

订阅专栏

FUNCTION ZRFC_REQUIRE_FORECAST .
*"----------------------------------------------------------------------
*"*"本地接口：
*"  IMPORTING
*"     VALUE(PERIOD_BEGIN) TYPE  PERXX
*"     VALUE(WERKS) TYPE  WERKS_D
*"  EXPORTING
*"     VALUE(RETURN) TYPE  BAPIRET2
*"     VALUE(OUTPUT) TYPE  ZSM8_SALESORDER_CREATE_OUTPUT
*"  TABLES
*"      LT_ZYC_OUT STRUCTURE  ZYC_OUT OPTIONAL
*"----------------------------------------------------------------------
  TABLES:T023T.
*----------------------------------------------------------------------*
* DESC：定义内表结构
*----------------------------------------------------------------------*
  TYPES: BEGIN OF T_TEMP,
         WERKS LIKE PBIM-WERKS,    "工厂
         PBDNR LIKE PBIM-PBDNR,   "需求计划号
         MATNR LIKE PBIM-MATNR,   "成品号
         PLNMG LIKE PBED-PLNMG,   "计划数量
         PERXX LIKE PBED-PERXX,   "计划期间
         C_MATNR LIKE ZCBOM-C_MATNR, "物料号
         MAKTX LIKE MAKT-MAKTX,    "物料描述
         MATKL LIKE ZCBOM-MATKL,   "物料组
         WGBEZ LIKE T023T-WGBEZ, "物料组描述
         MENGE LIKE ZCBOM-MENGE,   "组件数量
         QUNUM LIKE EQUK-QUNUM,    "配额协议编号
         QUOTE LIKE EQUP-QUOTE,   "配额
         LIFNR LIKE EORD-LIFNR,   "供应商编号
         NAME1 LIKE LFA1-NAME1,   "供应商名
       END OF T_TEMP,
*----------------------------------------------------------------------*
* DESC：最终数据同步表
*----------------------------------------------------------------------*
      BEGIN OF I_ITAB,
          LIFNR LIKE ZJDYS-LIFNR, "供应商                ZJDYS:季度预算计划
          NAME1 LIKE ZJDYS-NAME1, "供应商名称
          PERXX LIKE ZJDYS-PERXX, "期间
          MATNR LIKE ZJDYS-MATNR, "物料号
          MAKTX LIKE ZJDYS-MAKTX, "物料描述
          MATKL LIKE ZJDYS-MATKL, "物料组
          WGBEZ LIKE T023T-WGBEZ, "物料组描述
          WERKS LIKE PBIM-WERKS,  "工厂
          MENGE LIKE ZJDYS-PLNMG, "数量   ZJDYS-MENGE
          ERFME LIKE PBED-ERFME,  "单位
       END OF I_ITAB,

      BEGIN OF WZCBOM,
        MMATNR LIKE ZCBOM-M_MATNR,
        CMATNR LIKE ZCBOM-C_MATNR,
        MENGE LIKE ZCBOM-MENGE,
        CMAKTX LIKE MAKT-MAKTX,
        CMATKL LIKE MARA-MATKL,
        WGBEZ LIKE T023T-WGBEZ,
        END OF WZCBOM.

  DATA: ZCBOMINFO TYPE TABLE OF WZCBOM WITH HEADER LINE.
  DATA:TMP TYPE TABLE OF T_TEMP WITH HEADER LINE.

  SELECT ZCBOM~M_MATNR
        ZCBOM~C_MATNR "组件物料号                   ZCBOM: 每个成品物料零件用量表
        ZCBOM~MENGE "组件数量
       MAKT~MAKTX  "组件物料号的描述
       MARA~MATKL  "组件物料组
       T023T~WGBEZ
  INTO table ZCBOMINFO
  FROM ZCBOM
  INNER JOIN MAKT ON MAKT~MATNR = ZCBOM~C_MATNR
  INNER JOIN MARA ON MARA~MATNR = ZCBOM~C_MATNR
  LEFT JOIN T023T ON T023T~MATKL = MARA~MATKL and T023T~spras = 1
  WHERE zcbom~werks = WERKS AND  MAKT~SPRAS = 1.

**************获取需求预测*************
  SELECT
         PBIM~WERKS "工厂
         PBIM~PBDNR "需求计划号
         PBIM~MATNR "成品号
         PBED~PLNMG "计划数量
         PBED~PERXX "计划期间
   INTO (TMP-WERKS,TMP-PBDNR,TMP-MATNR,TMP-PLNMG,TMP-PERXX)
   FROM PBED
   JOIN PBIM ON ( PBIM~BDZEI = PBED~BDZEI AND PBIM~LOEVR = SPACE )
   WHERE PBED~ENTLI EQ  '3' AND
         PBED~PERXX GE PERIOD_BEGIN AND
         PBED~LOEVR = SPACE AND
         PBIM~PBDNR LIKE '%Y' AND
         PBIM~WERKS = WERKS.
**************获取组件物料号及数量****
    loop at ZCBOMINFO where MMATNR = TMP-MATNR.
      TMP-C_MATNR  = ZCBOMINFO-CMATNR.
      TMP-MAKTX  = ZCBOMINFO-CMAKTX.
      TMP-MATKL = ZCBOMINFO-CMATKL.
      TMP-MENGE = ZCBOMINFO-MENGE.
      TMP-WGBEZ = ZCBOMINFO-WGBEZ.
********* ******获取配额协议************
      SELECT SINGLE
        EQUK~QUNUM
        INTO  TMP-QUNUM
        FROM  EQUK
        WHERE EQUK~MATNR = TMP-C_MATNR  AND
              EQUK~BDATU GE SY-DATUM AND
              EQUK~VDATU LE SY-DATUM AND
              EQUK~WERKS = TMP-WERKS.
      IF TMP-QUNUM IS NOT INITIAL.
****************如果配额协议不为空，从EQUP中获取配额******
        SELECT EQUP~QUOTE
               EQUP~LIFNR
               LFA1~NAME1
               INTO (TMP-QUOTE,TMP-LIFNR,TMP-NAME1)
               FROM EQUP
               INNER JOIN LFA1 ON LFA1~LIFNR = EQUP~LIFNR AND LFA1~SPRAS = '1'
               WHERE EQUP~QUNUM = TMP-QUNUM.

          IF TMP-QUOTE > 0.
            APPEND TMP.
            CLEAR TMP-QUOTE.
            CLEAR TMP-LIFNR.
          ENDIF.

        ENDSELECT.
***************如果配额为空，从 ME03获取供应商信息及给定默认配额****
      ELSE.

        SELECT SINGLE EORD~LIFNR
                      LFA1~NAME1
               INTO (TMP-LIFNR,TMP-NAME1)
               FROM  EORD
               INNER JOIN LFA1 ON LFA1~LIFNR = EORD~LIFNR AND LFA1~SPRAS = '1'
               WHERE EORD~BDATU GE SY-DATUM AND
                     EORD~VDATU LE SY-DATUM AND
                     EORD~WERKS = TMP-WERKS AND
                     EORD~MATNR = TMP-C_MATNR.

        IF TMP-LIFNR IS NOT INITIAL.
          TMP-QUOTE = 100.
          APPEND TMP.
          CLEAR TMP-QUOTE.
          CLEAR TMP-LIFNR.
        ENDIF.
      ENDIF.
      CLEAR TMP-QUNUM.
    ENDloop.
    CLEAR TMP.
  ENDSELECT.

  if SY-subrc <> 0.
    return-type = 'E'.
    RETURN-MESSAGE = '没有查询到相关数据！'.
    OUTPUT-RETURN-MSGTY = return-type.
    OUTPUT-RETURN-MSGTX = RETURN-MESSAGE.
    return .
  endif.

  SORT TMP BY WERKS PERXX LIFNR C_MATNR MATKL.


  FIELD-SYMBOLS:<FS_ITAB> TYPE I_ITAB.   "I_TAB
  data: count type i. " 记录记录条数
  count = 0.

  LOOP AT TMP.
    READ TABLE LT_ZYC_OUT WITH KEY WERKS = TMP-WERKS
                             PERXX = TMP-PERXX
                             LIFNR = TMP-LIFNR
                             MATNR = TMP-C_MATNR
                             MATKL = TMP-MATKL BINARY SEARCH ASSIGNING <FS_ITAB>.
    IF SY-SUBRC <> 0.
      INSERT INITIAL LINE INTO LT_ZYC_OUT INDEX SY-TABIX ASSIGNING <FS_ITAB>.
      COUNT = COUNT + 1.
    ENDIF.
    <FS_ITAB>-WERKS = TMP-WERKS.  "工厂
    <FS_ITAB>-LIFNR = TMP-LIFNR.  "供应商
    <FS_ITAB>-NAME1 = TMP-NAME1.  "供应商名称
    <FS_ITAB>-PERXX = TMP-PERXX.  "期间
    <FS_ITAB>-MATNR = TMP-C_MATNR."物料号
    <FS_ITAB>-MAKTX = TMP-MAKTX.  "物料描述
    <FS_ITAB>-MATKL = TMP-MATKL.  "物料组
    <FS_ITAB>-WGBEZ = TMP-WGBEZ.  "物料组描述
    <FS_ITAB>-ERFME = 'EA'.
    <FS_ITAB>-MENGE = ( TMP-PLNMG * TMP-MENGE * TMP-QUOTE / 100 ) + <FS_ITAB>-MENGE.  "需求预测数量（加上<FS_ITAB>-MENGE；相同的工厂、相同而期间、相同的供应商、相同额物料的汇总数据）
*    CONCATENATE <FS_ITAB>-PERXX+0(4) '-' <FS_ITAB>-PERXX+4(2) INTO <FS_ITAB>-PERIOD.
*    <FS_ITAB>-NUMBER = <FS_ITAB>-MENGE.
  ENDLOOP.

  loop at LT_ZYC_OUT.
    CALL FUNCTION 'CONVERSION_EXIT_MATN1_OUTPUT'
      EXPORTING
        input  = LT_ZYC_OUT-MATNR
      IMPORTING
        output = LT_ZYC_OUT-MATNR.
    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
      EXPORTING
        input  = LT_ZYC_OUT-LIFNR
      IMPORTING
        output = LT_ZYC_OUT-LIFNR.
    MODIFY LT_ZYC_OUT.
    CLEAR LT_ZYC_OUT.
  ENDLOOP.

  if count = 0.
    return-type = 'I'.
    RETURN-MESSAGE = '没有查询到相关数据！'.
    OUTPUT-RETURN-MSGTY = RETURN-TYPE.
    OUTPUT-RETURN-MSGTX = RETURN-MESSAGE.
  else.
    return-type = 'S'.
    RETURN-MESSAGE = '查询到相关数据！'.
    OUTPUT-RETURN-MSGTY = RETURN-TYPE.
    OUTPUT-RETURN-MSGTX = RETURN-MESSAGE.
  ENDIF.

ENDFUNCTION.

SAT 分析

时间耗在 DB OPEN上面也就是不断的select 上面那有没有办法减少与数据库的交互呢，

利用CDS

@AbapCatalog.sqlViewName: 'ZVCDS_MM_QUOTE'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '配额查询'
define view ZCDS_MM_QUOTE 
as 
  select 
        equk.matnr   as  MATNR   ,
        equk.werks  as  WERKS   ,
        equk.bdatu  as  BDATU   ,
        equk.vdatu  as  VDATU   ,
        equk.qunum  as  QUNUM   ,
        equk.erdat  as  ERDAT   ,
        equk.ernam  as  ERNAM   ,
        equk.scmng  as  SCMNG   ,
        equk.changedon  as  CHANGEDON   ,
        equp.qupos  as  QUPOS   ,
        equp.beskz  as  BESKZ   ,
        equp.sobes  as  SOBES   ,
        equp.lifnr  as  LIFNR   ,
        equp.bewrk  as  BEWRK   ,
        equp.quote  as  QUOTE   ,
        equp.qubmg  as  QUBMG   ,
        equp.qumng  as  QUMNG   ,
        equp.maxmg  as  MAXMG   ,
        equp.verid  as  VERID   ,
        equp.maxls  as  MAXLS   ,
        equp.minls  as  MINLS   ,
        equp.rdprf  as  RDPRF   ,
        equp.kzein  as  KZEIN   ,
        equp.abrmg  as  ABRMG   ,
        equp.abper  as  ABPER   ,
        equp.abanz  as  ABANZ   ,
        equp.preih  as  PREIH   ,
        equp.ematn  as  EMATN   ,
        equp.plifz  as  PLIFZ   
        from equk 
        inner join equp on equk.qunum = equp.qunum

接口代码可以改写成

FUNCTION ZRFC_REQUIRE_FORECAST .
*"----------------------------------------------------------------------
*"*"本地接口：
*"  IMPORTING
*"     VALUE(PERIOD_BEGIN) TYPE  PERXX
*"     VALUE(WERKS) TYPE  WERKS_D
*"  EXPORTING
*"     VALUE(RETURN) TYPE  BAPIRET2
*"     VALUE(OUTPUT) TYPE  ZSM8_SALESORDER_CREATE_OUTPUT
*"  TABLES
*"      LT_ZYC_OUT STRUCTURE  ZYC_OUT OPTIONAL
*"----------------------------------------------------------------------
  TABLES:T023T.
*----------------------------------------------------------------------*
* DESC：定义内表结构
*----------------------------------------------------------------------*
  TYPES: BEGIN OF T_TEMP,
           WERKS   LIKE PBIM-WERKS,    "工厂
           PBDNR   LIKE PBIM-PBDNR,   "需求计划号
           MATNR   LIKE PBIM-MATNR,   "成品号
           PLNMG   LIKE PBED-PLNMG,   "计划数量
           PERXX   LIKE PBED-PERXX,   "计划期间
           C_MATNR LIKE ZCBOM-C_MATNR, "物料号
           MAKTX   LIKE MAKT-MAKTX,    "物料描述
           MATKL   LIKE ZCBOM-MATKL,   "物料组
           WGBEZ   LIKE T023T-WGBEZ, "物料组描述
           MENGE   LIKE ZCBOM-MENGE,   "组件数量
           QUNUM   LIKE EQUK-QUNUM,    "配额协议编号
           QUOTE   LIKE EQUP-QUOTE,   "配额
           LIFNR   LIKE EORD-LIFNR,   "供应商编号
           NAME1   LIKE LFA1-NAME1,   "供应商名
         END OF T_TEMP.
  DATA:TMP TYPE TABLE OF T_TEMP WITH HEADER LINE.
SELECT * INTO CORRESPONDING FIELDS OF TABLE TMP FROM ZCDSQUOTE
    WHERE PERXX GE PERIOD_BEGIN AND PBDNR LIKE '%Y' AND
        WERKS = WERKS.

  IF SY-SUBRC <> 0.
    RETURN-TYPE = 'E'.
    RETURN-MESSAGE = '没有查询到相关数据！'.
    OUTPUT-RETURN-MSGTY = RETURN-TYPE.
    OUTPUT-RETURN-MSGTX = RETURN-MESSAGE.
    RETURN .
  ENDIF.

  SORT TMP BY WERKS PERXX LIFNR C_MATNR MATKL.

  FIELD-SYMBOLS:<FS_ITAB> TYPE I_ITAB.   "I_TAB
  DATA: COUNT TYPE I. " 记录记录条数
  COUNT = 0.

  LOOP AT TMP.
    READ TABLE LT_ZYC_OUT WITH KEY WERKS = TMP-WERKS
                             PERXX = TMP-PERXX
                             LIFNR = TMP-LIFNR
                             MATNR = TMP-C_MATNR
                             MATKL = TMP-MATKL BINARY SEARCH ASSIGNING <FS_ITAB>.
    IF SY-SUBRC <> 0.
      INSERT INITIAL LINE INTO LT_ZYC_OUT INDEX SY-TABIX ASSIGNING <FS_ITAB>.
      COUNT = COUNT + 1.
    ENDIF.
    <FS_ITAB>-WERKS = TMP-WERKS.  "工厂
    <FS_ITAB>-LIFNR = TMP-LIFNR.  "供应商
    <FS_ITAB>-NAME1 = TMP-NAME1.  "供应商名称
    <FS_ITAB>-PERXX = TMP-PERXX.  "期间
    <FS_ITAB>-MATNR = TMP-C_MATNR."物料号
    <FS_ITAB>-MAKTX = TMP-MAKTX.  "物料描述
    <FS_ITAB>-MATKL = TMP-MATKL.  "物料组
    <FS_ITAB>-WGBEZ = TMP-WGBEZ.  "物料组描述
    <FS_ITAB>-ERFME = 'EA'.
    <FS_ITAB>-MENGE = ( TMP-PLNMG * TMP-MENGE * TMP-QUOTE / 100 ) + <FS_ITAB>-MENGE.  "需求预测数量（加上<FS_ITAB>-MENGE；相同的工厂、相同而期间、相同的供应商、相同额物料的汇总数据）
*    CONCATENATE <FS_ITAB>-PERXX+0(4) '-' <FS_ITAB>-PERXX+4(2) INTO <FS_ITAB>-PERIOD.
*    <FS_ITAB>-NUMBER = <FS_ITAB>-MENGE.
  ENDLOOP.

  LOOP AT LT_ZYC_OUT.
    CALL FUNCTION 'CONVERSION_EXIT_MATN1_OUTPUT'
      EXPORTING
        INPUT  = LT_ZYC_OUT-MATNR
      IMPORTING
        OUTPUT = LT_ZYC_OUT-MATNR.
    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
      EXPORTING
        INPUT  = LT_ZYC_OUT-LIFNR
      IMPORTING
        OUTPUT = LT_ZYC_OUT-LIFNR.
    MODIFY LT_ZYC_OUT.
    CLEAR LT_ZYC_OUT.
  ENDLOOP.

  IF COUNT = 0.
    RETURN-TYPE = 'I'.
    RETURN-MESSAGE = '没有查询到相关数据！'.
    OUTPUT-RETURN-MSGTY = RETURN-TYPE.
    OUTPUT-RETURN-MSGTX = RETURN-MESSAGE.
  ELSE.
    RETURN-TYPE = 'S'.
    RETURN-MESSAGE = '查询到相关数据！'.
    OUTPUT-RETURN-MSGTY = RETURN-TYPE.
    OUTPUT-RETURN-MSGTX = RETURN-MESSAGE.
  ENDIF.

ENDFUNCTION.

相同条件下执行时间从原来的5.5min - > 5s

写于20241105 浙江台州