ABAP CDS VIEW实现获取PO/PR审批节点信息

DeveloperMrMeng

已于 2023-07-13 19:03:22 修改

阅读量352

点赞数 1

分类专栏： SAP技术小知识文章标签：数据库 sql abap sap

于 2023-07-13 18:58:22 首次发布

本文链接：https://blog.csdn.net/DeveloperMrMeng/article/details/131706279

版权

SAP技术小知识专栏收录该内容

43 篇文章 19 订阅

订阅专栏

需求：查询出所有下一审批节点的审批码等于选择屏幕上输入的审批码的PR或者PO，并发送邮件给对应的审批者。

例如某订单的审批状态如上图所示，当选择界面审批码输入A1时，则该订单需要被显示到ALV上，但是这个逻辑无法通过SQL直接取出来。

EBAN 的 FRGZU 字段虽然存了当前审批到第几层，但存储的只是X，无法直接关联到T16FV或者T16FS来直接取到当前层级对应的审批码，所以一般来说可能只有下面这种方式：

取出PR/PO数据，依次调用函数 BAPI_REQUISITION_GETRELINFO 获取审批信息，根据 RELEASE_ALREADY_POSTED 和 RELEASE_FINAL 中的信息去判定；或者根据审批组和审批策略从 T16FV 或者 T16FS 表中取到对应信息。

总之都需要先把数据取出来，再根据 FRGZU 的内容长度去读取对应字段的信息才能知道，当前节点的审批码以及下级节点的审批码，这样会导致可能会取出来很多不需要的数据，而且效率会比较低下，考虑到这些问题，决定使用CDS VIEW来实现该功能，这也是S4HANA “Code Push Down” 理念的一种实现方式。

实现过程：

1.获取审批码对应的层级

根据X对应的字段位置，即可判断出该审批码所属层级。

2.根据 FRGZU 内容长度判定PO/PR当前审批层级以及下一层审批层级。（PR/PO同理）

3.关联这两张视图即可获取到当前节点审批码及下层节点审批码。

以上CDS VIEW部分完成，后续报表可以直接从最终视图按审批码直接拉出需要的数据，从而解决效率问题。

以下为完整代码：

获取审批码层级CDS VIEW：

@AbapCatalog.sqlViewName: 'ZPRMMV_REL_LEVEL'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Approval level'
define view ZPRMMT_REL_LEVEL
  as select from t16fv
{
  key frggr,
  key frgsx,
  key frgco,
      @EndUserText.label: 'Approval level'
      cast(
          case
              when frga1 = 'X' then '1'
              when frga2 = 'X' then '2'
              when frga3 = 'X' then '3'
              when frga4 = 'X' then '4'
              when frga5 = 'X' then '5'
              when frga6 = 'X' then '6'
              when frga7 = 'X' then '7'
              when frga8 = 'X' then '8'
              else ''
          end
      as abap.char( 1 ) ) as ZLEVEL
}

获取PR审批层级CDS VIEW：

@AbapCatalog.sqlViewName: 'ZPRMMV_PR_LEVEL'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'PR current&next approval level'
define view ZPRMMT_PR_LEVEL
  as select from eban
{
  key banfn,
  key bnfpo,
      frgkz,
      frgst,
      frggr,
      frgrl,
      frgzu,
      @EndUserText.label: 'Current level'
      cast( case LENGTH(frgzu)
          when 0 then '0'
          when 1 then '1'
          when 2 then '2'
          when 3 then '3'
          when 4 then '4'
          when 5 then '5'
          when 6 then '6'
          when 7 then '7'
          when 8 then '8'
          else ''
          end as abap.char( 1 ) ) as zlevel_curr,
      @EndUserText.label: 'Next level'
      cast( case LENGTH(frgzu)
          when 0 then '1'
          when 1 then '2'
          when 2 then '3'
          when 3 then '4'
          when 4 then '5'
          when 5 then '6'
          when 6 then '7'
          when 7 then '8'
          when 8 then ''
          else ''
          end as abap.char( 1 ) ) as zlevel_next
}
where
      frgst != ''
  and frggr != ''

最终集成视图（PR）包含当前节点审批码及下层审批码CDS VIEW：

@AbapCatalog.sqlViewName: 'ZPRMMV_RELS_PR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Purchase request approval status'
define view ZPRMMT_RELSTAT_PR
  as select from    eban             as a
    inner join      ZPRMMT_PR_LEVEL  as b on  a.banfn = b.banfn
                                          and a.bnfpo = b.bnfpo
    left outer join ZPRMMT_REL_LEVEL as c on  a.frggr       = c.frggr
                                          and a.frgst       = c.frgsx
                                          and b.zlevel_curr = c.ZLEVEL
    left outer join ZPRMMT_REL_LEVEL as d on  a.frggr       = d.frggr
                                          and a.frgst       = d.frgsx
                                          and b.zlevel_next = d.ZLEVEL
{
  key a.banfn,
  key a.bnfpo,
      a.bsart,
      a.loekz,
      a.statu,
      a.frgkz,
      a.frgrl,
      a.gsfrg,
      a.frgst,
      a.frggr,
      b.zlevel_curr,
      @EndUserText.label: 'Current approval code'
      cast( case when c.frgco is null then '' else c.frgco end as abap.char( 2 ) )                                                                  as zrelcode_curr,
      b.zlevel_next,
      @EndUserText.label: 'Next approval code'
      cast( case when d.frgco is null then '' else d.frgco end as abap.char( 2 ) )                                                                  as zrelcode_next,
      a.ekgrp,
      a.werks,
      a.ernam,
      a.afnam,
      a.matnr,
      a.txz01,
      a.menge,
      a.meins,
      @Semantics.currencyCode
      a.waers                                                                                                                                       as Waers,
      a.rlwrt,
      @EndUserText.label: 'Total item amount'
      @Semantics.amount.currencyCode: 'Waers'
      cast( division( ( a.preis * a.menge ),cast( ( case a.peinh when 0 then 1 else a.peinh end ) as abap.dec( 5, 0 ) ),2 ) as abap.curr( 15, 2 ) ) as value_item,
      a.frgdt,
      a.lfdat,
      a.webaz,
      a.matkl,
      a.dispo,
      a.lifnr,
      a.banpr
}

获取PO审批层级CDS VIEW：

@AbapCatalog.sqlViewName: 'ZPRMMV_PO_LEVEL'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'PO current&next approval level'
define view ZPRMMT_PO_LEVEL
  as select from ekko
{
  key ebeln,
      frggr,
      frgsx,
      frgke,
      frgrl,
      frgzu,
      @EndUserText.label: 'Current level'
      cast( case LENGTH(frgzu)
          when 0 then '0'
          when 1 then '1'
          when 2 then '2'
          when 3 then '3'
          when 4 then '4'
          when 5 then '5'
          when 6 then '6'
          when 7 then '7'
          when 8 then '8'
          else ''
          end as abap.char( 1 ) ) as zlevel_curr,
      @EndUserText.label: 'Next level'
      cast( case LENGTH(frgzu)
          when 0 then '1'
          when 1 then '2'
          when 2 then '3'
          when 3 then '4'
          when 4 then '5'
          when 5 then '6'
          when 6 then '7'
          when 7 then '8'
          when 8 then ''
          else ''
          end as abap.char( 1 ) ) as zlevel_next
}
where
      frgsx != ''
  and frggr != ''

最终集成视图（PO）包含当前节点审批码及下层审批码CDS VIEW：

@AbapCatalog.sqlViewName: 'ZPRMMV_RELS_PO'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Purchase Order approval status'
define view ZPRMMT_RELSTAT_PO
  as select from    ekko             as a
    inner join      ekpo             as e on a.ebeln = e.ebeln
    inner join      ZPRMMT_PO_LEVEL  as b on a.ebeln = b.ebeln
    left outer join ZPRMMT_REL_LEVEL as c on  a.frggr       = c.frggr
                                          and a.frgsx       = c.frgsx
                                          and b.zlevel_curr = c.ZLEVEL
    left outer join ZPRMMT_REL_LEVEL as d on  a.frggr       = d.frggr
                                          and a.frgsx       = d.frgsx
                                          and b.zlevel_next = d.ZLEVEL
{
  key a.ebeln,
  key e.ebelp,
      a.bsart,
      a.loekz,
      a.frgke,
      a.frgrl,
      a.frgsx,
      a.frggr,
      b.zlevel_curr,
      @EndUserText.label: 'Current approval code'
      cast( case when c.frgco is null then '' else c.frgco end as abap.char( 2 ) ) as zrelcode_curr,
      b.zlevel_next,
      @EndUserText.label: 'Next approval code'
      cast( case when d.frgco is null then '' else d.frgco end as abap.char( 2 ) ) as zrelcode_next,
      a.ekgrp,
      a.ekorg,
      e.werks,
      a.ernam,
      e.afnam,
      e.matnr,
      e.txz01,
      e.menge,
      e.meins,
      e.netwr,
      a.waers,
      a.procstat
}

问题补充：

在实际取数的过程中，发现一条数据的对应的邮件地址取不出来，检查数据发现其实没什么不同，但实际上问题就出在视图中的zrelcode_next字段上。