业务需求:物料清单有很多层,业务部门要求开发成品BOM的结构报表,明确子BOM和主BOM之间的关联性。
软件环境:SAP business One 9.2(PL10)
解决方案:在SAP B1后台数据库创建存储过程,或在SAP B1前端的查询管理器创建查询报表,参考代码如下:
ALTER PROCEDURE [dbo].[BOMzhankai]
---exec [dbo].[BOMzhankai] 'A70100000017' //调用存储过程
@father varchar(50)
AS
BEGIN
WITH Bom(Father, code, BomLevel,sort,warehouse,Qty,issue) AS
(
SELECT father, code, 1 AS bomlevel,convert(varchar(255), father+code),warehouse,quantity,issuemthd
FROM itt1
where father