CDS 进销存报表功能实现

CDS 进销存报表功能实现

业务需求概览

SAP标准功能一般有两个常用的进销存报表.

  • MB5B
  • J3RFLVMOBVEDH

其中MB5B特殊库存不支持合并显示.同时这两张报表为旧技术(ECC)实现,随着业务量积累效率明显越来越低.零售业中基本无法使用.

综上所述,本功能充分利用S4 HANA新特性,用CDS实现进销存报表.

增加差异金额的分摊计算逻辑,使工厂层的评估变动分摊到库存明细级,最终实现库存明细级别的完整的数量,金额的进销存逻辑.

该项目包含在GITHUB个人工具包中.
https://github.com/OutKeal/abap_zwftools

主要功能实现

1. 物料凭证预处理

功能方法.ZWFT_INV_CLASS=>MSEG_WRITE( ).本方法主要两个地方调用,物料凭证保存增强和独立程序ZWFT_INV_MSEG.独立程序主要处理逻辑变更,配置表更时重新生成数据,增强负责正常数据处理.

  1. 处理物料凭证金额,使一些0金额业务例如移库也有金额

    • 若本币金额(dmbtr)为空且外部金额(exbwr)不为空,则金额取外部金额.

    • 若本币金额为空,则用公式本币金额=数量*评估总金额/评估总数量,计算本币金额.

  2. 借贷(shkzg)为H的行,数量金额都切换为负.

  3. 从配置表zwft_inv_type匹配获取进销存类型.

  4. 获取库存评估编号(增强时从结构VM07M,重新跑时从MATDOC)

  5. 处理特殊库存关键字

    • O/K,特殊库存关键字为供应商编号

    • W,特殊库存关键字为客户编号

    • E/T,特殊库存关键字为mat_kdauf+mat_kdpos

  6. 通过物料凭证的录入日期/录入时间,生成时间戳.

  7. 315/316等凭证,补全其发货行.

  8. 写入表ZWFT_INV_MSEG

*Importing	XMSEG	TYPE TY_T_MSEG	表 MSEG
*Importing	VM07M	TYPE TY_T_VM07M OPTIONAL	带有结构 VM07M 的表
  METHOD mseg_write.
    init_inv_type( ).
    DATA: in_mseg TYPE TABLE OF zwft_inv_mseg.
    DATA: in_mseg_ex TYPE TABLE OF zwft_inv_mseg.

    IF vm07m IS INITIAL.
      SELECT mblnr,mjahr,zeile,kalnr
        FROM matdoc
        FOR ALL ENTRIES IN @xmseg
    WHERE mblnr = @xmseg-mblnr
        INTO TABLE @DATA(lt_doc).
      SORT lt_doc BY mblnr mjahr zeile.
    ENDIF.

    CHECK xmseg[] IS NOT INITIAL.
    LOOP AT xmseg INTO DATA(l_xmseg).
      READ TABLE zwft_inv_class=>inv_type INTO DATA(l_inv_type)
                                                              WITH KEY bwart = l_xmseg-bwart
                                                                                shkzg = l_xmseg-shkzg.
      APPEND INITIAL LINE TO in_mseg ASSIGNING FIELD-SYMBOL(<in_mseg>).
      MOVE-CORRESPONDING l_xmseg TO <in_mseg>.

      <in_mseg>-satnr = zwft_single_read=>mara( <in_mseg>-matnr )-satnr.
      IF <in_mseg>-satnr IS INITIAL.
        <in_mseg>-satnr = <in_mseg>-matnr.
      ENDIF.

      IF <in_mseg>-dmbtr IS INITIAL AND l_xmseg-exbwr IS NOT INITIAL.
        <in_mseg>-dmbtr = l_xmseg-exbwr.
      ELSEIF <in_mseg>-dmbtr IS INITIAL.
        IF l_xmseg-bustw IS INITIAL AND <in_mseg>-lbkum IS NOT INITIAL.
          <in_mseg>-dmbtr = <in_mseg>-erfmg * <in_mseg>-salk3 / <in_mseg>-lbkum.
        ENDIF.
      ENDIF.

      IF <in_mseg>-shkzg = 'H'.
        <in_mseg>-erfmg  = - <in_mseg>-erfmg.
        <in_mseg>-dmbtr  = - <in_mseg>-dmbtr.
      ENDIF.

      <in_mseg>-record_type = 'MDOC'.
      <in_mseg>-inv_type = l_inv_type-inv_type.
      CLEAR l_inv_type.
      CASE <in_mseg>-sobkz.
        WHEN 'O' OR 'K'.
          <in_mseg>-ssnum = <in_mseg>-lifnr.
        WHEN 'W'.
          <in_mseg>-ssnum = <in_mseg>-kunnr.
        WHEN 'E' OR 'T'.
          <in_mseg>-ssnum = <in_mseg>-mat_kdauf && <in_mseg>-mat_kdpos.
      ENDCASE.

      CONVERT DATE l_xmseg-cpudt_mkpf TIME l_xmseg-cputm_mkpf
        INTO TIME STAMP <in_mseg>-timestamp TIME ZONE sy-zonlo.
      IF vm07m IS NOT INITIAL.
        READ TABLE vm07m INTO DATA(l_vm07m) WITH KEY zeilv = l_xmseg-zeile.
        IF sy-subrc EQ 0.
          <in_mseg>-kalnr = l_vm07m-kaln1.
        ENDIF.
      ELSEIF lt_doc IS NOT INITIAL.
        READ TABLE lt_doc INTO DATA(ls_doc) WITH KEY mblnr = l_xmseg-mblnr mjahr = l_xmseg-mjahr zeile = l_xmseg-zeile BINARY SEARCH.
        IF sy-subrc EQ 0.
          <in_mseg>-kalnr = ls_doc-kalnr.
        ENDIF.
      ENDIF.

      IF <in_mseg>-bwart = '315' OR <in_mseg>-bwart = '316'.
        APPEND INITIAL LINE TO in_mseg_ex ASSIGNING FIELD-SYMBOL(<in_mseg_ex>).
        <in_mseg_ex> = <in_mseg>.
        <in_mseg_ex>-record_type = 'MDOC_CP'.
        <in_mseg_ex>-shkzg = COND #( WHEN <in_mseg_ex>-shkzg = 'S' THEN 'H' ELSE 'H' ).
        <in_mseg_ex>-erfmg  = - <in_mseg_ex>-erfmg.
        <in_mseg_ex>-dmbtr  = - <in_mseg_ex>-dmbtr.
      ENDIF.

    ENDLOOP.

    IF in_mseg_ex IS NOT INITIAL.
      APPEND LINES OF in_mseg_ex TO in_mseg.
    ENDIF.

    IF in_mseg IS NOT INITIAL.
      MODIFY zwft_inv_mseg FROM TABLE in_mseg.
    ENDIF.
  ENDMETHOD.

2. CDS 时点库存

带参数的CDS视图,截止到某一天(小于)之前的物料凭证求和,简单实现库存的查询.

需要处理的部分在物料凭证预处理时已经处理完成.

@AbapCatalog.sqlViewName: 'ZWFT_INV_STOCK'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '进销存时点库存'
define view ZWFT_CDS_INV_STOCK 
  with parameters
    P_endDate  : vdm_v_end_date
as select from zwft_inv_mseg
{   
    bukrs,
    werks,
    lgort,
    satnr,
    matnr,
    charg,
    sobkz,
    ssnum,
    sum( erfmg ) as menge,
    sum( dmbtr ) as dmbtr

}

where budat_mkpf < $parameters.P_endDate //进销存需求,截止日期+1传入   
group by 
    bukrs,
    werks,
    lgort,
    satnr,
    matnr,
    charg,
    sobkz,
    ssnum


3. CDS 进销存发生额

简单实现开始到结束日期的,按进销存类型分类汇总的发生数量/发生金额.

  • I 入库数量/金额
  • O 出库数量/金额
  • D 差异金额 (来源请看后续差异计算逻辑)
  • E 未分配 (原则上不应该有未分配,补充配置表ZWFT_INV_TYPE)
@AbapCatalog.sqlViewName: 'ZWFT_INV_CHANGE'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '进销存发生额'


define view zwft_cds_inv_change 
  with parameters
    P_startDate  : vdm_v_start_date,
    P_endDate  : vdm_v_end_date
as select from zwft_inv_mseg
{
    bukrs,
    werks,
    lgort,
    satnr,
    matnr,
    charg,
    sobkz,
    ssnum,
    sum( case inv_type when 'I' then erfmg end ) as MENGE_I,
    sum( case inv_type when 'I' then dmbtr end ) as DMBTR_I,
    sum( case inv_type when 'O' then erfmg end ) as MENGE_O,
    sum( case inv_type when 'O' then dmbtr end ) as DMBTR_O,
    sum( case inv_type when 'D' then dmbtr end ) as dmbtr_d,
    sum( case inv_type when ' ' then erfmg end ) as MENGE_E,
    sum( case inv_type when ' ' then dmbtr end ) as DMBTR_E
    
}
where budat_mkpf >= $parameters.P_startDate
and budat_mkpf < $parameters.P_endDate
group by 
    bukrs,
    werks,
    lgort,
    satnr,
    matnr,
    charg,
    sobkz,
    ssnum

4. CDS 进销存实现

  1. 通过时点库存CDS视图获取期末库存数量金额
  2. 左关联时点库存CDS视图获取期初库存数量金额.
  3. 左关联进销存发生额CDS视图获取出库/入库/差异等数量金额.
  4. 根据特殊库存关键字还原特殊库存的关键字段
@AbapCatalog.sqlViewName: 'ZWFT_INV'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '进销存报表'
define view ZWFT_CDS_INV 
  with parameters
    P_startDate  : vdm_v_start_date,
    P_endDate  : vdm_v_end_date
as select from ZWFT_CDS_INV_STOCK(P_endDate: $parameters.P_endDate ) as end
left outer join ZWFT_CDS_INV_STOCK( P_endDate: $parameters.P_startDate ) as start
on start.bukrs = end.bukrs
and start.werks = end.werks
and start.lgort = end.lgort
and start.satnr = end.satnr
and start.matnr = end.matnr
and start.charg = end.charg
and start.sobkz = end.sobkz
and start.ssnum = end.ssnum

left outer join zwft_cds_inv_change(P_startDate:$parameters.P_startDate,P_endDate:$parameters.P_endDate) as change
on change.bukrs = end.bukrs
and change.werks = end.werks
and change.lgort = end.lgort
and change.satnr = end.satnr
and change.matnr = end.matnr
and change.charg = end.charg
and change.sobkz = end.sobkz
and change.ssnum = end.ssnum
{
    end.bukrs,
    end.werks,
    end.lgort,
    end.satnr,
    end.matnr,
    end.charg,
    end.sobkz,
    end.ssnum,
    @EndUserText.label: '期初数量'
    start.menge as menge_start,
    @EndUserText.label: '期初金额'
    start.dmbtr as dmbtr_start,
    @EndUserText.label: '入库数量'
    change.MENGE_I as menge_in,
    @EndUserText.label: '入库金额'
    change.DMBTR_I as DMBTR_in,
    @EndUserText.label: '出库数量'
    change.MENGE_O as menge_out,
    @EndUserText.label: '出库金额'
    change.DMBTR_O as DMBTR_OUT,
    @EndUserText.label: '差异金额' 
    change.dmbtr_d as dmbtr_diff,
    @EndUserText.label: '异常数量'
    change.MENGE_E as menge_E,
    @EndUserText.label: '异常金额'
    change.DMBTR_E as DMBTR_E,
    @EndUserText.label: '期末数量'
    end.menge as menge_end,
    @EndUserText.label: '期末金额'
    end.dmbtr as dmbtr_end,
    case when end.sobkz = 'E' then LEFT(end.ssnum,10) end as KDAUF,
    case when end.sobkz = 'E' then RIGHT(end.ssnum,6) end as KDPOS,
    case when end.sobkz = 'T' then LEFT(end.ssnum,10) end as VBELN_VL,
    case when end.sobkz = 'T' then RIGHT(end.ssnum,6) end as POSNR_VL,
    case when end.sobkz = 'W' then LEFT(end.ssnum,10) end as KUNNR,
    case when end.sobkz = 'O' then LEFT(end.ssnum,10) end as LIFNR
}


差异金额处理

主要功能中实现的逻辑少了一部分发票校验差异\存货移动差异\重估差异等处理,会导致收发存金额不平,且结余对不上FI总账科目.

本块功能实现非物料凭证库存的库存金额调整的计算/分摊逻辑,完善进销存金额计算.

1.CDS 会计凭证调整明细

获取ACDOCA关键财务凭证明细.

关键条件:

  • and acdoca.ktosl = ‘BSX’
  • acdoca.awtyp <> ‘MKPF’
  • 包含acdoca.awtyp = ‘MKPF’ and acdoca.blart = ‘PR’,此类型为跨期记账自动差异处理生成的会计凭证.

获取关键字段:

  • timestamp,时间戳
  • kalnr,评估号
  • wsl,差异金额,带正负符号
@AbapCatalog.sqlViewName: 'ZWFT_INV_DIFF'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '获取进销存差异'
define view ZWFT_CDS_INV_DIFF as select from acdoca
inner join bkpf
    on acdoca.rbukrs = bkpf.bukrs
    and acdoca.belnr = bkpf.belnr
    and acdoca.gjahr = bkpf.gjahr
{
    acdoca.awtyp,
    acdoca.belnr,
    acdoca.gjahr,
    acdoca.docln,
    acdoca.rbukrs,
    acdoca.drcrk,
    acdoca.budat,
    acdoca.matnr,
    acdoca.werks,
    acdoca.timestamp,
    acdoca.kalnr,
    acdoca.wsl
}
where ( acdoca.awtyp <> 'MKPF' or ( acdoca.awtyp = 'MKPF' and acdoca.blart = 'PR' ) )
and acdoca.ktosl = 'BSX'

2. 差异分摊

程序ZWFT_INV_DIFF,按照工厂,记账日期,物料获取差异明细,调用zwft_inv_class=>diff_split( )方法分摊差异.

通过CDS视图ZWFT_CDS_INV_S_TS,获取时间戳评估号的节点明细库存.

关键条件:

  • budat_mkpf <= :p_endDate(会计凭证过账日的月最后一天)

  • timestamp < :p_TIMESTAMP(会计凭证时间戳)

@AbapCatalog.sqlViewName: 'ZWFT_INV_S_TS'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '时间戳的库存'
define view ZWFT_CDS_INV_S_TS
  with parameters
    p_TIMESTAMP  : timestamp,
    p_endDate : vdm_v_end_date
as select from zwft_inv_mseg
{
    bukrs,
    werks,
    lgort,
    satnr,
    matnr,
    charg,
    sobkz,
    ssnum,
    kalnr,
    sum(erfmg) as menge,
    sum(dmbtr) as dmbtr
}
where budat_mkpf <= :p_endDate
and timestamp < :p_TIMESTAMP
group by 
    bukrs,
    werks,
    lgort,
    satnr,
    matnr,
    charg,
    sobkz,
    ssnum,
    kalnr

按照数量比例分摊每个评估号(大概等同于工厂+物料)的库存差异,分摊到最细的库存级别(库存地点,特殊库存),整理数据写入ZWFT_INV_MSEG表.

关键写入字段:

  • 进销存类型ZWFT_INV,默认D
  • 移动类型,BWART,默认DIF.
  • 凭证号相关字段,写会计凭证的凭证号/公司/年份,拆分行增加二级行号
  • 库存关键字段(工厂,物料,库位,特殊库存),取库存拆分明细对应信息.
  • 金额计算最后一行用减法.

可以考虑每天定时作业运动前一天的ZWFT_INV_DIFF处理差异的卷算.注意时序很重要.

总结

分摊完毕后,将物料/评估范围级的财务金额记账,分摊到了库存明细级别.报表曾将进销存类别D直接显示为差异金额,期初期末包含对应的差异金额.完成进销存计算闭环.

进销存报表

程序ZWFT_INV.

筛选条件:

  • 工厂
  • 仓库
  • 物料类型
  • 物料组
  • 物料编号
  • 开始日期
  • 结束日期

报表ALV显示:

​ 进销存视图数据已经足够,简单获取仓库/物料的描述,销售订单的对应款号即完成.

  • 53
    点赞
  • 38
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值