S/4 HANA中的MATDOC和MATDOC_EXTRACT

最新推荐文章于 2024-04-15 00:03:31 发布

buzul

最新推荐文章于 2024-04-15 00:03:31 发布

阅读量1.7k

点赞数 1

分类专栏：技术

原文链接：https://www.colorgg.com

版权

技术专栏收录该内容

559 篇文章 16 订阅

订阅专栏

最近做了销售和物料管理方面的一些需求，因此对S/4中的MM的数据模型有了一定的了解。目前网络已经有一些介绍物料凭证表MATDOC的文章，内容好像不是很详细，另外也没发现介绍库存变更数据表MATDOC_EXTRACT的文章。我打算把自己知道的一点东西写下来分享。水平所限，如有不正确的地方，请务必评论指正。

本文将介绍2点内容，

传统的物料凭证表MKPF，MSEG和新表MATDOC间的关系
传统的库存表如MARD, MCHB, MSKU, MSLB, MARDH, MCHBH, MSKUH, MSLBH等和新表MATDOC以及MATDOC_EXTRACT的关系

此外，一些广为流传的文章（比如SAP S/4 Hana On-premise Edition 1511做了哪些简化、SAP S/4HANA 1610后勤之变）中提出S4的SAP中的库存数据会从MATDOC表中实时计算，本文将证明这种说法是不准确的：至少对于MARD等标准表而言，其中的数据来自于对MATDOC_EXTRACT的汇总，而非MATDOC。

本文链接：https://www.cnblogs.com/hhelibeb/p/9361187.html

原创内容，转载请注明

replacement object

网络上的个别文章提到S4中不再存在MKPF和MARD等表，数据都来自于CDS。这种说法不是很准确，事实上MKPF等表依然是存在的，只不过其中的数据被CDS entity替换掉了，这种CDS entity就是所谓replacement object.。MKPF和MSEG中的数据全部来自MATDOC，MARD中的全部数据来自于MATDOC_EXTRACT。而类似于FAGLFLEXT这种视图，它们的表则是真的不存在了。SE11中查看的话可以看出区别：

这种区别看似无所谓，实际上对开发人员而言是有区别。使用Open SQL对包含replacement object的表的访问通常会被重定向到replacement object，但在某些情况下存在例外（具体参看文档）。这种名实不符的情况有时会导致意外的bug，可以参见笔者之前的踩坑经历。

物料凭证

MKPF, MSEG和MATDOC之间的关系相对简单，目前我试着把接到的需求里的MKPF和MSEG都转换成MATDOC，开发效率有一些提升，程序的工作情况良好。

MKPF表的replacement object是NSDM_E_MKPF，这个CDS entity是直接从MATDOC中取数的，条件为record_type = 'MDOC'和header_counter = 1，代码如下，可以重点看where语句部分，

(注：前缀NSDM的意思可能是New, Simplified Data Model)

@AbapCatalog.sqlViewName:      'NSDM_V_MKPF'
@EndUserText.label:            'MKPF Compatibility View'
@DataAging.noAgingRestriction: 'false'

@ObjectModel.usageType.sizeCategory: 'L'
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ClientHandling.algorithm: #AUTOMATED
@AccessControl.authorizationCheck: #NOT_ALLOWED
@AbapCatalog.viewEnhancementCategory: #PROJECTION_LIST
define view nsdm_e_MKPF  as
select 
key mandt, 
key mblnr, 
key mjahr,
vgart,
blart,
blaum,
bldat,
budat,
cpudt,
cputm,
aedat,
usnam,
tcode,
xblnr,
bktxt,
frath,
frbnr,
wever,
xabln,
awsys,
bla2d,
tcode2,
bfwms,
exnum,
spe_budat_uhr,
spe_budat_zone,
le_vbeln,
spe_logsys,
spe_mdnum_ewm,
gts_cusref_no,
fls_rsto,
msr_active
/*
Fields which do belong to APPENDs on MSEG will be provided via EXTEND VIEW to this view.
Hence, Industries having also APPENDs can use the same technique 
and just because the alphabetical sequence of APPENDs is also valid for EXTEND VIEW the compability will be ensured.
The name of the IS EXTEND VIEW has to be the same as the APPEND. Rules are:
ABAP Catalog Objekt: <Append>_V
DDL Source <Append>_DDL
CDS View = <Append>_E
The alphabetical order of the EXTEND VIEW in the final proxy view is according to the name of sqlViewAppendName
and not accroding to DDL source name or the EXTEND VIEW name! Hence there is still a restriction on 16 chars!
*/
/*
DDL sources of known extensions so far:
JVMKPF_DDL
MILL_MKPF_DDL
*/
from matdoc
where record_type = 'MDOC'
  and header_counter = 1

MSEG和它差不多，replacement object是NSDM_E_MSEG，同样从MATDOC中获取数据，条件则只有record_type = 'MDOC'，代码略过不贴。

使用MATDOC代替MKPF和MSEG，可以减少不必要的关联次数。另外，也有stock_qty和consumption_qty等方便计算的字段，利用它们可以减少相关逻辑处理代码，提高开发速度。

库存

一些文章提到S4中的库存数据是从MATDOC中实时汇总得出的，的确，MATDOC中包含计算库存所需的全部数据。但是这个表的数据是物料凭证级别的，随着时间的进行，SAP系统内会积累大量的物料凭证，从这样海量的数据中汇总库存，似乎有些奢侈。例如FICO模块中的ACDOCA表，就是从年初余额开始汇总，来获取余额的（见前文：S/4 HANA中的ACDOCT和FAGLFLEXT），而不是汇总历史上的每一笔凭证。

通过对各个库存表的replacement object的阅读追踪，我发现实际上库存数据来自于另一个表：MATDOC_EXTRACT。这是一个实在的透明表，并不存在replacement object：

NOTE 2246602 中提到了这个表：

为了减轻（从MATDOC中进行聚合运算时因数据量大导致的性能下降的）影响，SAP引入了第二个表MATDOC_EXTRACT，从中计算实际库存数据。MATDOC_EXTRACT有一小部分来自MATDOC的字段，在物料凭证过账时，每当有数据被插入到MATDOC中，也会有数据被插入MATDOC_EXTRACT中。MATDOC_EXTRACT里的数据会被定期压缩，比如，假设有以下两条数据：

Date:20.11.2015; Material:4711; Plant:0001; StorageLocation:0001; StockIdentifier:01; SpecialStock:blank; Quantity: 10,0; Unit: PC
Date:23.11.2015; Material:4711; Plant:0001; StorageLocation:0001; StockIdentifier:01; SpecialStock:blank; Quantity: -3,0; Unit: PC

会被压缩为：

Date:PrecompactingDate; Material:4711; Plant:0001; StorageLocation:0001; StockIdentifier:01; SpecialStock:blank; Quantity: 7,0; Unit: PC

新的压缩后的数据会被插入MATDOC_EXTRACT，两条被压缩的数据会被从中删除。通过这种做法，表

MATDOC_EXTRACT中的总记录数和用于聚合的记录数都会定期减少，因此，对库存的查询（相比从MATDOC中进行）会有更好的性能。

该过程被称为“预压缩”（precompacting），默认作为期末结算（period end closing）的一部分运行。如果因此导致期末结算性能下降，也可以自行安排定期后台作业。

以下通过MARD的replacement object，NSDM_E_MARD为例，我们来观察下它是如何从MATDOC_EXTRACT中获取数据的。源代码：

@AbapCatalog.sqlViewName:      'NSDM_V_MARD'
@EndUserText.label:            'MARD Compatibility View'
@DataAging.noAgingRestriction: 'false'

@ObjectModel.usageType.sizeCategory: 'L'
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ClientHandling.algorithm: #AUTOMATED
@AccessControl.authorizationCheck: #NOT_ALLOWED
@AbapCatalog.viewEnhancementCategory: #PROJECTION_LIST

define view nsdm_e_mard
  as select from    mard             as t
    left outer join nsdm_e_mard_diff as m on  t.mandt = m.mandt
                                          and t.matnr = m.matnr
                                          and t.werks = m.werks
                                          and t.lgort = m.lgort
  association [0..1] to E_Productstoragelocation as _ActiveExtension on  $projection.matnr = _ActiveExtension.Product
                                                                     and $projection.werks = _ActiveExtension.Plant
                                                                     and $projection.lgort = _ActiveExtension.StorageLocation
{
  key t.mandt,
  key t.matnr,
  key t.werks,
  key t.lgort,

      t.pstat,
      t.lvorm,

      case
      when m.gjper = '0000000'
      or m.gjper is null then
      t.lfgja
      else
      cast(substring(m.gjper, 1, 4) as abap.numc(4))
      end                                                         as lfgja,

      case
      when m.gjper = '0000000'
      or m.gjper is null then
      t.lfmon
      else
      cast(substring(m.gjper, 6, 2) as abap.numc(2))
      end                                                         as lfmon,

      t.sperr,
      case when m.labst is null then 0 else m.labst end           as labst,      // vvv note 2249780
      case when m.umlme is null then 0 else m.umlme end           as umlme,
      case when m.insme is null then 0 else m.insme end           as insme,
      case when m.einme is null then 0 else m.einme end           as einme,
      case when m.speme is null then 0 else m.speme end           as speme,
      case when m.retme is null then 0 else m.retme end           as retme,
      case when m.vmlab is null then 0 else m.vmlab end           as vmlab,
      case when m.vmuml is null then 0 else m.vmuml end           as vmuml,
      case when m.vmins is null then 0 else m.vmins end           as vmins,
      case when m.vmein is null then 0 else m.vmein end           as vmein,
      case when m.vmspe is null then 0 else m.vmspe end           as vmspe,
      case when m.vmret is null then 0 else m.vmret end           as vmret,      // ^^^ note 2249780
      t.kzill,
      t.kzilq,
      t.kzile,
      t.kzils,
      t.kzvll,
      t.kzvlq,
      t.kzvle,
      t.kzvls,
      t.diskz,
      t.lsobs,
      t.lminb,
      t.lbstf,
      t.herkl,
      t.exppg,
      t.exver,
      t.lgpbe,
      case when m.klabs is null then 0 else m.klabs end           as klabs,      // vvv note 2249780
      case when m.kinsm is null then 0 else m.kinsm end           as kinsm,
      case when m.keinm is null then 0 else m.keinm end           as keinm,
      case when m.kspem is null then 0 else m.kspem end           as kspem,      // ^^^ note 2249780
      t.dlinl,
      t.prctl,
      t.ersda,
      case when m.vklab is null then 0 else m.vklab end           as vklab,      // note 2249780
      case when m.vkuml is null then 0 else m.vkuml end           as vkuml,      // note 2249780
      t.lwmkb,
      t.bskrf,
      'X'                                                         as mdrue,
      t.mdjin,
      --field added for extensibility
      t.dummy_stl_incl_eew_ps,
      --Fields added for EA-RETAIL(FASHION)
      t.fsh_salloc_qty_s,
      case when m./cwm/labst is null then 0 else m./cwm/labst end as /cwm/labst, // note 2413597
      case when m./cwm/insme is null then 0 else m./cwm/insme end as /cwm/insme, // note 2413597
      case when m./cwm/einme is null then 0 else m./cwm/einme end as /cwm/einme, // note 2413597
      case when m./cwm/speme is null then 0 else m./cwm/speme end as /cwm/speme, // note 2413597
      case when m./cwm/retme is null then 0 else m./cwm/retme end as /cwm/retme, // note 2413597
      case when m./cwm/umlme is null then 0 else m./cwm/umlme end as /cwm/umlme, // note 2413597
      case when m./cwm/klabs is null then 0 else m./cwm/klabs end as /cwm/klabs, // note 2413597
      case when m./cwm/kinsm is null then 0 else m./cwm/kinsm end as /cwm/kinsm, // note 2413597
      case when m./cwm/keinm is null then 0 else m./cwm/keinm end as /cwm/keinm, // note 2413597
      case when m./cwm/kspem is null then 0 else m./cwm/kspem end as /cwm/kspem, // note 2413597
      case when m./cwm/vmlab is null then 0 else m./cwm/vmlab end as /cwm/vmlab, // note 2413597
      case when m./cwm/vmins is null then 0 else m./cwm/vmins end as /cwm/vmins, // note 2413597
      case when m./cwm/vmein is null then 0 else m./cwm/vmein end as /cwm/vmein, // note 2413597
      case when m./cwm/vmspe is null then 0 else m./cwm/vmspe end as /cwm/vmspe, // note 2413597
      case when m./cwm/vmret is null then 0 else m./cwm/vmret end as /cwm/vmret, // note 2413597
      case when m./cwm/vmuml is null then 0 else m./cwm/vmuml end as /cwm/vmuml // note 2413597

}

可以看到，labst等度量，完全来自于与透明表MARD左连接的CDS entity：nsdm_e_mard_diff，观察这个entity，可以看到，labst等entity其实原本都是stock_qty，只是根据lbbsa字段和sobkz字段的值的不同，被分成不同的度量列。

@AbapCatalog.sqlViewName:      'NSDM_V_MARD_DIFF'
@EndUserText.label:            'MARD Compatibility View: Differentiation'
@DataAging.noAgingRestriction: 'false'

@ObjectModel.usageType.sizeCategory: 'L'
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ClientHandling.algorithm: #AUTOMATED
@AccessControl.authorizationCheck: #NOT_ALLOWED
@AbapCatalog.viewEnhancementCategory: #PROJECTION_LIST
define view nsdm_e_mard_diff as
select from nsdm_e_mard_agg 
{
key mandt, 
key matnr, 
key werks, 
key lgort,

max(gjper_max) as gjper,

sum(case lbbsa when '01' then case sobkz when '' then stock_qty else 0 end else 0 end) as labst,
sum(case lbbsa when '04' then case sobkz when '' then stock_qty else 0 end else 0 end) as umlme,
sum(case lbbsa when '02' then case sobkz when '' then stock_qty else 0 end else 0 end) as insme,
sum(case lbbsa when '08' then case sobkz when '' then stock_qty else 0 end else 0 end) as einme,       
sum(case lbbsa when '07' then case sobkz when '' then stock_qty else 0 end else 0 end) as speme,
sum(case lbbsa when '03' then case sobkz when '' then stock_qty else 0 end else 0 end) as retme,

cast(0 as abap.quan(13, 3)) as vmlab,
cast(0 as abap.quan(13, 3)) as vmuml, 
cast(0 as abap.quan(13, 3)) as vmins,
cast(0 as abap.quan(13, 3)) as vmein,
cast(0 as abap.quan(13, 3)) as vmspe,
cast(0 as abap.quan(13, 3)) as vmret,

sum(case lbbsa when '01' then case sobkz when 'K' then stock_qty else 0 end else 0 end) as klabs,           
sum(case lbbsa when '02' then case sobkz when 'K' then stock_qty else 0 end else 0 end) as kinsm,
sum(case lbbsa when '08' then case sobkz when 'K' then stock_qty else 0 end else 0 end) as keinm,
sum(case lbbsa when '07' then case sobkz when 'K' then stock_qty else 0 end else 0 end) as kspem,  

sum(case lbbsa when '01' then case sobkz when '' then stock_vkwrt else 0 end else 0 end) as vklab,
sum(case lbbsa when '04' then case sobkz when '' then stock_vkwrt else 0 end else 0 end) as vkuml,

sum(case lbbsa when '01' then case sobkz when '' then /cwm/stock_qty else 0 end else 0 end) as /cwm/labst,
sum(case lbbsa when '04' then case sobkz when '' then /cwm/stock_qty else 0 end else 0 end) as /cwm/umlme,
sum(case lbbsa when '02' then case sobkz when '' then /cwm/stock_qty else 0 end else 0 end) as /cwm/insme,
sum(case lbbsa when '08' then case sobkz when '' then /cwm/stock_qty else 0 end else 0 end) as /cwm/einme,       
sum(case lbbsa when '07' then case sobkz when '' then /cwm/stock_qty else 0 end else 0 end) as /cwm/speme,
sum(case lbbsa when '03' then case sobkz when '' then /cwm/stock_qty else 0 end else 0 end) as /cwm/retme,
cast(0 as abap.quan(13, 3)) as /cwm/vmlab,
cast(0 as abap.quan(13, 3)) as /cwm/vmuml, 
cast(0 as abap.quan(13, 3)) as /cwm/vmins,
cast(0 as abap.quan(13, 3)) as /cwm/vmein,
cast(0 as abap.quan(13, 3)) as /cwm/vmspe,
cast(0 as abap.quan(13, 3)) as /cwm/vmret,
sum(case lbbsa when '01' then case sobkz when 'K' then /cwm/stock_qty else 0 end else 0 end) as /cwm/klabs,           
sum(case lbbsa when '02' then case sobkz when 'K' then /cwm/stock_qty else 0 end else 0 end) as /cwm/kinsm,
sum(case lbbsa when '08' then case sobkz when 'K' then /cwm/stock_qty else 0 end else 0 end) as /cwm/keinm,
sum(case lbbsa when '07' then case sobkz when 'K' then /cwm/stock_qty else 0 end else 0 end) as /cwm/kspem,

'X' as mdrue 

}
group by mandt, matnr, werks, lgort

这个lbbsa字段是什么意思呢？它是货物移动的库存类型（库存标识符），域是NSDM_LBBSA，包含固定值（第一行为空值）：

      与库存不相关
01   未限制使用的库存
02   在检库存
03   退货
04   库存调拨（存储位置）
05   库存调拨（工厂）
06   在途存货
07   已冻结的库存
08   限制使用的存货
09   配套空包装
10   评估收货已冻结库存

sobkz为特殊库存标识。也就是说这是一种行转列的变换。举个例子，代码sum(case lbbsa when '01' then case sobkz when '' then stock_qty else 0 end else 0 end) as labst, 代表着如果库存类型是“未限制使用的库存”，特殊库存标识为“K”，则它属于MARD中的LABST，即“未限制使用的估价的库存”

继续查看下一层entity：nsdm_e_mard_agg ，

@AbapCatalog.sqlViewName:      'NSDM_V_MARD_AGG'
@EndUserText.label:            'MARD Compatibility View: Aggregation'
@DataAging.noAgingRestriction: 'false'

@ObjectModel.usageType.sizeCategory: 'L'
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ClientHandling.algorithm: #AUTOMATED
@AccessControl.authorizationCheck: #NOT_ALLOWED
@AbapCatalog.viewEnhancementCategory: #PROJECTION_LIST

define view nsdm_e_mard_agg as
select from matdoc_extract 
{
key mandt, 
key matbf     as matnr, 
key werks, 
key lgort_sid as lgort,

lbbsa_sid     as lbbsa,
sobkz,

sum(stock_qty_l2)      as stock_qty,
sum(stock_vkwrt_l2)    as stock_vkwrt,
max(gjper_curr_per)    as gjper_max,
sum(/cwm/stock_qty_l2) as /cwm/stock_qty
}
where stock_ind_l2 = ''
  and (((sobkz = '' or sobkz = 'K')
         and (lbbsa_sid = '01' or lbbsa_sid = '02' or lbbsa_sid = '07' or lbbsa_sid = '08'))
   or   (sobkz = ''
         and (lbbsa_sid = '03' or lbbsa_sid = '04')))
 
group by mandt, matbf, werks, lgort_sid, lbbsa_sid, sobkz