什么是BOM展开
在制造业和工程领域,BOM是“Bill of Materials”的缩写,指的是一份详细列出产品所需物料和组成结构的清单。BOM记录了产品的层级结构,包括最顶层的总装配件、子装配件和零部件,直到最底层的原材料。每个物料在BOM中都有对应的编号、名称、描述和数量等信息,还可以包括物料的供应商、价格、规格等详细信息。
SAP BOM层级展开是SAP自带的基础功能,展开后会出现如下边所示的层级结构。(图源网络)
级别编码是第一第二第三依次展开,会有对应的BOM清单、物料、单位、数量等信息。
使用HANA的存储过程实现BOM层级展开
SAP BOM速度不算快,一个BOM都要十几秒,即使只是查询当天的BOM,也需要等待一段时间。
因此决定使用HANA展开BOM,提高BOM展开的速度。
(下边的为初始版本,之后如果有修改会对代码作更新)
这里说两个需要注意的点吧:
1.直接用STPO表(BOM明细)是不行的,因为里边有一些无用的明细需要删除,所以对这张表作了处理。
2.第一层级单独处理,因为需要到销售订单表去找对应的物料信息,之后的层级再循环跑
ALTER PROCEDURE PRO_BOM_LEVEL_INIT (OUT INFO NVARCHAR(1000))
/**************************************************************
功能说明:展开BOM层级
参数描述:INFO 调试存储过程时返回的信息
执行语句样例:CALL PRO_BOM_LEVEL_INIT(?)
功能说明:
模仿SAP BOM层级展开
**************************************************************/
AS
--循环计数
V_NUM INTEGER;
BEGIN
/*
前置处理:
STPO表(BOM明细)存放了所有BOM明细数据信息,包括新增、修改、删除
因此需要关联AENR(变更号所在的表)与STAS状态表,取出发生变更的记录。
对于STPO表的处理
新增:不需要处理,因为STPO表本来就存在
删除:找到STAS打了删除标记的(LKENZ='X'),直接过滤
修改:这种情况,STPO表的STLNR(BOM清单),STVKN(继承节点号)是重复的,需要找到最大的STLKN(BOM 项目节点号)
综上,处理就是直接过滤删除的,取最大的STLKN(BOM 项目节点号)
*/
TEMP_STPO=
SELECT STPO.STLNR,STPO.STVKN,STPO.VBELN_SLT_P,VBPOS_SLT_P,MAX(STPO.STLKN) AS STLKN
FROM "PRD800_MID"."STPO" STPO
JOIN (
-- 这部分取当日需要处理的所有销售订单号,包括新增和修改
-- 当日修改
SELECT
DISTINCT
S.VBELN_SLT AS VBELN --销售凭证
FROM "PRD800_MID"."AENR" A
LEFT JOIN "PRD800_MID"."STAS" S
ON A.AENNR=S.AENNR
WHERE 1=1
AND A.ANDAT=to_char(add_days(current_date,-1),'yyyymmdd') -- 时间取前一天
AND S.STLTY='K'
UNION
-- 当日新增
SELECT DISTINCT VBELN
FROM "PRD800_MID"."KDST" K
WHERE K.ANDAT=to_char(add_days(current_date,-1),'yyyymmdd') -- 时间取前一天
) TEMP ON STPO.VBELN_SLT_P = TEMP.VBELN
LEFT JOIN(
-- 这部分找出发生变更的记录,实际上需要取的是删除,因为删除标记只在STAS里边有。如果是修改的,直接取STPO表的最大STLKN即可
SELECT S.STLNR,S.STVKN,S.VBELN_SLT,S.LKENZ,S.STLKN
FROM "PRD800_MID"."AENR" A
JOIN "PRD800_MID"."STAS" S ON A.AENNR=S.AENNR
) T1
ON STPO.STLNR = T1.STLNR AND STPO.STVKN = T1.STVKN AND STPO.VBELN_SLT_P = T1.VBELN_SLT AND STPO.STLKN = T1.STLKN
WHERE IFNULL(T1.LKENZ,'') !='X' -- 过滤掉删除的
AND STPO.STLTY='K' -- 类型为订单BOM
GROUP BY STPO.STLNR,STPO.STVKN,STPO.VBELN_SLT_P,VBPOS_SLT_P
;
-- 删除需要重新跑的销售订单
DELETE FROM BI.BOM_LEVEL B
WHERE EXISTS
(
SELECT 1
FROM :TEMP_STPO TEMPS
WHERE TEMPS.VBELN_SLT_P =B.VBELN_SLT_P
-- AND K.ANDAT=CURRENT_DATE
-- AND K.VBELN='0001261237'
)
;
/*
步骤一:找到第一层级
①根据上边前置处理,取得需要跑的销售订单号
②在VBAP中取出来字段MATNR(物料号),就是第一层级的BOM号对应的父项物料号,KWMENG(订单数量),就是第一层级的BOM号对应的父项物料的基本数量;
③再根据第一层级的物料+销售订单号,在KDST、STKO、STPO表中,依次根据BOM号和行项目号,就得出从顶层到最低层的BOM结构。其中这里的IDNRK是下一层级的物料号
注意:这里不能直接用stpo表,应该与我们预处理的stpo关联过滤。如果不作这个处理,删除和修改的都会在stpo出现,与实际结果不同
*/
INSERT INTO BI.BOM_LEVEL
SELECT
T1.BSTKD, -- 内部流转号
'1' AS BOM_LEVEL, -- BOM层级
K.STLNR, --BOM号(物料清单)
S.POSNR, --BOM 项目号
T1.MATNR, --父项物料号
S.IDNRK, --BOM 组件
S.STLTY, --类别
S.STLKN, --BOM 项目节点号
S.STPOZ, --内部计数器
S.DATUV, --有效起始日期
S.AENNR, --变更编号
S.ANDAT, --日期记录创建于
S.AEDAT, --最后更改的日期
S.MENGE, --组件数量
S.MEINS, --组件计量单位
S.ERSKZ, --标识:备件
S.ZGG, --规格
S.ZREMARK, --备注
S.ZODS, --ODS
S.ZGDMK, --工地模块
S.ZCGLX, --采购类型参数
S.SFPTWL, --是否配套物料
S.VBELN_SLT_P, --销售凭证
S.VBPOS_SLT_P, --销售订单行项目号
M.SBDKZ, --独立/集中
NOW()AS LOAD_TIME,
K.STLNR||S.POSNR AS MATNR_SORT
FROM (
-- 取得第一层级的销售订单信息和物料号
SELECT
DISTINCT
V2.BSTKD,
TEMPS.VBELN_SLT_P AS VBELN,
V.MATNR, -- 父项物料号
V.KWMENG -- 组件数量
FROM :TEMP_STPO TEMPS
JOIN "PRD800_MID"."VBAP" V ON TEMPS.VBELN_SLT_P = V.VBELN AND TEMPS.VBPOS_SLT_P = V.POSNR
JOIN "PRD800_MID"."VBKD" V2 ON TEMPS.VBELN_SLT_P = V2.VBELN
) T1
JOIN "PRD800_MID"."KDST" K ON T1.MATNR =K.MATNR AND T1.VBELN=K.VBELN
JOIN "PRD800_MID"."STPO" S ON T1.VBELN = S.VBELN_SLT_P AND K.STLNR=S.STLNR
JOIN :TEMP_STPO TEMPS ON TEMPS.STLNR = S.STLNR AND TEMPS.STVKN = S.STVKN AND TEMPS.STLKN = S.STLKN AND TEMPS.VBELN_SLT_P = S.VBELN_SLT_P
JOIN "PRD800_MID"."MARC" M ON K.WERKS=M.WERKS AND S.IDNRK =M.MATNR
WHERE S.STLTY='K'
;
/*
步骤二:开始循环跑剩下的9个层级
其实就是上边步骤一③,用结果表上一层级的物料(IDNRK)+销售订单号,在KDST、STKO、STPO表中,依次根据BOM号和行项目号,就得出从顶层到最低层的BOM结构。其中这里的IDNRK是下一层级的物料号
*/
FOR V_NUM IN 2 .. 10 DO
INSERT INTO BI.BOM_LEVEL
SELECT
B.BSTKD, -- 内部流转号
V_NUM AS BOM_LEVEL, -- BOM层级
K.STLNR, --BOM号(物料清单)
S.POSNR, --BOM 项目号
B.IDNRK, --父项物料号
S.IDNRK, --BOM 组件
S.STLTY, --类别
S.STLKN, --BOM 项目节点号
S.STPOZ, --内部计数器
S.DATUV, --有效起始日期
S.AENNR, --变更编号
S.ANDAT, --日期记录创建于
S.AEDAT, --最后更改的日期
S.MENGE*B.MENGE, --组件数量
S.MEINS, --组件计量单位
S.ERSKZ, --标识:备件
S.ZGG, --规格
S.ZREMARK, --备注
S.ZODS, --ODS
S.ZGDMK, --工地模块
S.ZCGLX, --采购类型参数
S.SFPTWL, --是否配套物料
S.VBELN_SLT_P, --销售凭证
S.VBPOS_SLT_P, --销售订单行项目号
M.SBDKZ, --独立/集中
NOW() AS LOAD_TIME,
MATNR_SORT||K.STLNR||S.POSNR AS MATNR_SORT
FROM BI.BOM_LEVEL B
JOIN "PRD800_MID"."KDST" K ON B.IDNRK =K.MATNR AND B.VBELN_SLT_P=K.VBELN
JOIN "PRD800_MID"."STPO" S ON B.VBELN_SLT_P = S.VBELN_SLT_P AND K.STLNR=S.STLNR
JOIN :TEMP_STPO TEMPS ON TEMPS.STLNR = S.STLNR AND TEMPS.STVKN = S.STVKN AND TEMPS.STLKN = S.STLKN AND TEMPS.VBELN_SLT_P = S.VBELN_SLT_P
JOIN "PRD800_MID"."MARC" M ON K.WERKS=M.WERKS AND S.IDNRK =M.MATNR
WHERE K.STLAN=1 AND S.STLTY='K' AND B.BOM_LEVEL =V_NUM-1
AND B.LOAD_TIME>=TO_TIMESTAMP(CURRENT_DATE) -- 这里加一个过滤条件,是因为结果表存了所有的历史数据,只用今天插入的数据作为第一层级
;
V_NUM = V_NUM+1;
END FOR;
END;
优化后的速度有多快?
70万条数据(这是我第二次跑,所以中间涉及delete操作,数据翻倍),差不多15s左右可以跑完。