38 lines
1.4 KiB
Transact-SQL
Executable File
38 lines
1.4 KiB
Transact-SQL
Executable File
/* ============================================
|
||
* 迁移脚本:Product_BOM_Detail 加 IsVirtual 字段
|
||
* 用途:BOM 展开时,物料属性为虚拟(5)/配置(9)的子件需要保留在快照里
|
||
* (保证 BuildTotalUsage 用量倍数传递),同时打上 IsVirtual=1 标识
|
||
* 供 BuildTotalUsage 跳过累计、RecalcScheduleRequirement 过滤
|
||
*
|
||
* 执行方式:连接业务库直接执行(幂等,已存在字段不重复加)
|
||
* ============================================ */
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM sys.columns
|
||
WHERE object_id = OBJECT_ID(N'Product_BOM_Detail') AND name = 'IsVirtual'
|
||
)
|
||
BEGIN
|
||
ALTER TABLE Product_BOM_Detail
|
||
ADD IsVirtual BIT NOT NULL DEFAULT 0; -- 0=真实子件, 1=虚拟件/配置件(仅用于用量传递,不参与需求计算)
|
||
PRINT '已添加 IsVirtual 字段';
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
PRINT 'IsVirtual 字段已存在,跳过';
|
||
END
|
||
GO
|
||
|
||
-- 为历史快照数据回填:虚拟件/配置件打标
|
||
-- 物料属性在 T_BD_MATERIAL.FERPCLSID(5=虚拟,9=配置)
|
||
-- 按 ChildMaterialId 关联物料主数据回填
|
||
UPDATE D SET IsVirtual = 1
|
||
FROM Product_BOM_Detail D
|
||
INNER JOIN T_BD_MATERIAL M
|
||
ON M.FNUMBER = D.ChildMaterialId
|
||
AND M.FDELETED = 0
|
||
AND M.FCOMPANYID = 986610
|
||
WHERE M.FERPCLSID IN ('5', '9')
|
||
AND D.IsVirtual = 0;
|
||
PRINT '历史数据回填完成';
|
||
GO
|