用友U9 BOM全阶展开SQL代码深度解析从递归逻辑到业务映射在ERP系统实施过程中物料清单(BOM)的展开查询是生产制造模块的核心功能之一。用友U9作为国内领先的ERP解决方案其BOM展开逻辑采用了递归CTE(Common Table Expression)技术实现多层级物料关系的遍历。本文将深入剖析这段看似复杂但设计精巧的SQL代码帮助开发者掌握其技术实现原理和业务映射关系。1. BOM数据结构与递归CTE基础1.1 U9 BOM核心表结构用友U9的BOM数据主要存储在三个关键表中CBO_BOMMaster存储BOM主表信息包含BOM版本、生效日期等基础属性CBO_BOMComponent存储BOM组件明细记录子件与母件的用量关系CBO_ItemMaster存储物料主数据包含料品编码、名称、规格等基础信息这三个表通过外键关联构成了U9 BOM数据模型的基础框架。理解它们之间的关系是解析BOM展开逻辑的前提。1.2 递归CTE的工作原理递归CTE是SQL中处理层级数据的强大工具其基本结构包含WITH RECURSIVE cte_name AS ( -- 基础查询非递归部分 SELECT ... FROM ... UNION ALL -- 递归查询部分 SELECT ... FROM cte_name JOIN ... ) SELECT * FROM cte_name;递归CTE的执行过程分为三个阶段执行基础查询生成初始结果集将上一步结果作为输入执行递归部分查询重复步骤2直到返回空集为止在BOM展开场景中这种特性非常适合处理父项-子项的无限级联关系。2. 代码模块逐层解析2.1 基础CTEbomComponent这段代码首先定义了bomComponentCTE用于获取BOM组件的基本信息with bomComponent (FbomMaster,itemCode,itemName,itemSpecs,用量,母件底数,料品的形态属性,发料方式) as ( select c3.BOMMaster FbomMaster, c4.Code itemCode, c4.Name itemName, c4.SPECS itemSpecs, c3.UsageQty 用量, c3.ParentQty 母件底数, (case when c4.ItemFormAttribute0 then 模型 when c4.ItemFormAttribute1 then 按订单拣货 -- 其他形态属性映射... end) 料品的形态属性, (case when c3.IssueStyle0 then 推式 when c3.IssueStyle1 then 工序倒冲 -- 其他发料方式映射... end) 发料方式 from CBO_BOMComponent c3 left join CBO_ItemMaster c4 on c3.ItemMaster c4.ID )关键点解析通过CBO_BOMComponent和CBO_ItemMaster的关联获取组件详细信息使用CASE语句将数字枚举值转换为业务可读的文本用量和母件底数字段决定了物料的标准消耗量2.2 中间CTEFbomComponent和fullBom接下来定义了两个辅助CTE来构建完整BOM结构,FbomComponent (bomMaster,FitemCode, FitemName,FitemSpecs) as ( select m1.ID bomMaster, c2.Code FitemCode, c2.Name FitemName, c2.SPECS FitemSpecs from CBO_BOMMaster m1 left join CBO_ItemMaster c2 on m1.ItemMaster c2.ID ) ,fullBom (bomMaster,FitemCode, FitemName, FitemSpecs, FbomMaster , itemCode, itemName, itemSpecs, 用量, 母件底数, 料品的形态属性, 发料方式 ) as ( select f.bomMaster bomMaster, f.FitemCode FitemCode, f.FitemName FitemName, f.FitemSpecs FitemSpecs, c.FbomMaster FbomMaster, c.itemCode itemCode, c.itemName itemName, c.itemSpecs itemSpecs, c.用量 用量, c.母件底数 母件底数, c.料品的形态属性 料品的形态属性, c.发料方式 发料方式 from FbomComponent f left join bomComponent c on c.FbomMaster f.bomMaster )这两个CTE的作用FbomComponent获取BOM母件的基本信息fullBom将母件信息与子件信息关联形成完整的BOM结构2.3 递归CTEtree_teset核心的递归逻辑在tree_tesetCTE中实现, tree_teset ( bomMaster,FitemCode, FitemName, FitemSpecs, FbomMaster , itemCode, itemName, itemSpecs, 用量, 母件底数, 料品的形态属性, 发料方式 ) as ( -- 基础查询获取完整的BOM结构 select bomMaster,FitemCode, FitemName, FitemSpecs, FbomMaster , itemCode, itemName, itemSpecs, 用量, 母件底数, 料品的形态属性, 发料方式 from fullBom --where FitemCode 这里添加单个物料料号 --查询单个BOM union all -- 递归查询通过物料编码关联实现层级展开 select t1.bomMaster,t1.FitemCode, t1.FitemName, t1.FitemSpecs, t1.FbomMaster , t1.itemCode, t1.itemName, t1.itemSpecs, t1.用量, t1.母件底数, t1.料品的形态属性, t1.发料方式 from fullBom t1 join tree_teset t2 on t1.FitemCode t2.itemCode ) select * from tree_teset递归逻辑的关键在于基础查询获取初始BOM结构递归部分通过t1.FitemCode t2.itemCode条件实现层级关联每次递归都会将子件作为新的父件继续向下展开3. 业务逻辑深度剖析3.1 料品形态属性的业务含义代码中对ItemFormAttribute字段的映射揭示了U9丰富的物料类型体系数值类型名称业务含义0模型产品原型或模板1按订单拣货根据客户订单直接拣货2按订单装配需要根据订单进行装配4委外加工件需要外协加工的物料6虚拟不存在实物仅用于逻辑计算10制造件需要内部生产的物料理解这些类型对于正确配置BOM至关重要。例如虚拟件常用于简化BOM结构处理通用组件实现逻辑分组3.2 发料方式的生产控制IssueStyle字段决定了物料如何发放到生产现场(case when c3.IssueStyle0 then 推式 when c3.IssueStyle1 then 工序倒冲 when c3.IssueStyle2 then 完工倒冲 when c3.IssueStyle3 then 开工倒冲 when c3.IssueStyle4 then 不发料 end) 发料方式不同发料方式的适用场景推式发料传统方式提前将物料发到工位工序倒冲完成特定工序后自动扣减库存完工倒冲产品完工后一次性扣减所有组件不发料不进行库存扣减适用于虚拟件等3.3 用量计算与母件底数BOM展开中的关键计算字段c3.UsageQty 用量, -- 单件用量 c3.ParentQty 母件底数 -- 母件基准数量实际消耗量计算公式实际消耗 用量 × (母件需求数量 / 母件底数)例如母件A的底数为10子件B的用量为2需要生产母件A数量为30则子件B的实际需求 2 × (30/10) 64. 高级应用与性能优化4.1 单物料查询的实现代码中注释掉的过滤条件可用于查询特定物料的BOM结构--where FitemCode 这里添加单个物料料号 --查询单个BOM实际应用时可以动态传入参数例如DECLARE ItemCode VARCHAR(50) A1001; WITH bomComponent AS (...) SELECT * FROM tree_teset WHERE FitemCode ItemCode;4.2 递归深度控制无限递归可能导致性能问题可通过OPTION子句限制最大递归深度SELECT * FROM tree_teset OPTION (MAXRECURSION 100);4.3 物料层级标记在实际应用中通常需要显示物料在BOM中的层级关系。可以修改递归CTE添加层级计数, tree_teset (..., Level) as ( -- 基础查询 select ..., 0 as Level from fullBom union all -- 递归查询 select ..., t2.Level 1 from fullBom t1 join tree_teset t2 on t1.FitemCode t2.itemCode )4.4 用量累计计算对于成本核算等场景可能需要计算顶层到当前物料的累计用量, tree_teset (..., CumulativeUsage) as ( -- 基础查询 select ..., 用量 as CumulativeUsage from fullBom union all -- 递归查询 select ..., t2.CumulativeUsage * t1.用量 from fullBom t1 join tree_teset t2 on t1.FitemCode t2.itemCode )5. 实际项目中的经验分享在多个U9实施项目中BOM展开查询常遇到以下典型问题性能瓶颈对于深层次、多分支的BOM结构递归查询可能较慢。解决方案包括添加适当的索引如ItemCode、BOMMaster字段考虑使用临时表存储中间结果对非常复杂的BOM采用分批处理虚拟件处理虚拟件不参与实际库存扣减但会影响MRP计算。确保正确标记虚拟件的ItemFormAttribute在MRP参数中配置虚拟件的处理规则替代料管理标准BOM展开不包含替代料信息需要额外关联替代料表LEFT JOIN CBO_BOMSubstitute sub ON c3.ID sub.BOMComponent版本控制生产环境中通常需要指定BOM版本WHERE m1.Version 2023-01有效性检查增加日期有效性过滤确保获取正确的BOM版本WHERE GETDATE() BETWEEN m1.EffectiveDate AND m1.ExpiryDate对于需要频繁执行BOM展开的场景建议将这段代码封装为存储过程添加必要的参数控制和性能优化措施。在二次开发报表时可以基于此基础查询进一步关联成本、库存等信息构建完整的物料分析解决方案。