U8 存储过程展开BOM表

ksuser 发表于 2017-4-7 16:16:14

CREATEPROCEDURE .
@erpdbname NVARCHAR(20),
@bomid INT,
@qty DECIMAL(28, 6)
---适用版本U8
AS
--展开方式：阶列式
--BOM状态：3
--是否使用计划比例：否
--采购件是否展开：否
--BOM展开单位：Inventory_Sub.iBOMExpandUnitType = 1

DECLARE @count INT;
DECLARE @bomlevel INT;
DECLARE @sql NVARCHAR(4000);

IF (RIGHT(@erpdbname, 2) <> '..')
BEGIN
   SET @erpdbname = @erpdbname
END;

--创建临时表
CREATE TABLE #temp
(
   Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
   BomLevel INT NULL,
   BomId INT NULL,

   OpComponentId INT NULL,
   OpSeq NCHAR(4) NULL,
   FVFlag TINYINT NULL,
   ByProductFlag BIT NULL,
   WipType TINYINT NULL,
   WhCode NVARCHAR(10) NULL,

   ParentId INT NULL,
   ParentInvCode NVARCHAR(20) NULL,
   ParentInvName NVARCHAR(255) NULL,

   ChildId INT NULL,
   childVersion nvarchar(50) NULL,
   ChildInvCode NVARCHAR(20) NULL,
   ChildInvName NVARCHAR(255) NULL,
   ChildInvCCode NVARCHAR(255) NULL,
   ChildInvStd NVARCHAR(255) NULL,
   ChildComUnitCode NVARCHAR(35) NULL,
   ChildBSelf BIT NULL,
   ChildBPurchase BIT NULL,
   ChildBProxyForeign BIT NULL,
   ChildBomId INT NULL,

   BaseQtyN DECIMAL(28, 6) NULL,
   BaseQtyD DECIMAL(28, 6) NULL,
   ParentScrap DECIMAL(28, 6) NULL,
   CompScrap DECIMAL(28, 6) NULL,

   Qty DECIMAL(28, 6) NULL,
   BaseQtyNCalc DECIMAL(28, 6) NULL,
   BaseQtyDCalc DECIMAL(28, 6) NULL,

);

--插入第一条初始化数据
SET @sql = '';
SET @sql = @sql + 'INSERT INTO #temp(bomlevel, childid, childVersion ,childbomid, baseqtyn, baseqtyd, baseqtyncalc, baseqtydcalc,qty) ';
SET @sql = @sql + 'SELECT -1 AS bomlevel, bp.parentid AS childid,b.version, b.bomid AS childbomid, 1 AS baseqtyn, 1 AS baseqtyd, 1 AS baseqtyncalc, 1 AS baseqtydcalc,1 AS qty ';
SET @sql = @sql + 'FROM ' + @erpdbname + 'bom_bom b ';
SET @sql = @sql + 'INNER JOIN ' + @erpdbname + 'bom_parent bp ';
SET @sql = @sql + 'ON b.bomid = bp.bomid ';
SET @sql = @sql + 'WHEREbp.parentid NOT IN (SELECT partid FROM ' + @erpdbname + 'bas_part WHERE invcode IN (SELECT cinvcode FROM ' + @erpdbname + 'inventory WHERE bself = 0 AND bpurchase = 1)) ';
SET @sql = @sql + 'AND b.bomid = ' + CAST(@bomid AS VARCHAR) + '; ';
EXEC(@sql);

--准备开始循环
SET @count = 1;
SET @bomlevel = -1;
WHILE @count > 0 BEGIN

   --插入所有子件
   SET @sql = '';
   SET @sql = @sql + 'INSERT INTO #temp(bomlevel, bomid, ';
   SET @sql = @sql + 'opcomponentid, opseq, fvflag, byproductflag, wiptype, whcode, ';
   SET @sql = @sql + 'parentid,childVersion, childid, ';
   SET @sql = @sql + 'baseqtyn, baseqtyd, parentscrap, compscrap, ';
   SET @sql = @sql + 'baseqtyncalc, baseqtydcalc,qty) ';

   SET @sql = @sql + 'SELECT ' + CAST(@bomlevel+1 AS NVARCHAR) + ' AS bomlevel, b.bomid, ';
   SET @sql = @sql + 'bc.opcomponentid, bc.opseq, bc.fvflag, bc.byproductflag, bco.wiptype, bco.whcode, ';
   SET @sql = @sql + 'bp.parentid,b.version, bc.componentid, ';
   SET @sql = @sql + 'bc.baseqtyn, bc.baseqtyd, bp.parentscrap, bc.compscrap, ';
   SET @sql = @sql + '(CASE WHEN bc.fvflag = 0 THEN 1 ELSE t.baseqtyncalc END) * bc.baseqtyn AS baseqtyncalc, ';
   SET @sql = @sql + '(CASE WHEN bc.fvflag = 0 THEN 1 ELSE t.baseqtydcalc END) * bc.baseqtyd AS baseqtydcalc, ';
   SET @sql = @sql + '(CASE WHEN bc.fvflag = 0 THEN 1 ELSE t.qty END) * (CASE bc.fvflag WHEN 0 THEN (1 + bc.compscrap / 100) ELSE (1 + bc.compscrap / 100) / (1 - bp.parentscrap / 100) END) AS qty ';

   SET @sql = @sql + 'FROM (SELECT childbomid, baseqtyncalc, baseqtydcalc, qty FROM #temp WHERE bomlevel = ' + CAST(@bomlevel AS NVARCHAR) + ' AND childbomid IS NOT NULL) t ';
   SET @sql = @sql + 'INNER JOIN ' + @erpdbname + 'bom_bom b ';
   SET @sql = @sql + 'ON t.childbomid = b.bomid ';
   SET @sql = @sql + 'INNER JOIN ' + @erpdbname + 'bom_parent bp ';
   SET @sql = @sql + 'ON b.bomid = bp.bomid ';
   SET @sql = @sql + 'INNER JOIN ' + @erpdbname + 'bom_opcomponent bc ';
   SET @sql = @sql + 'ON b.bomid = bc.bomid ';
   SET @sql = @sql + 'INNER JOIN ' + @erpdbname + 'bom_opcomponentopt bco ';
   SET @sql = @sql + 'ON bc.optionsid = bco.optionsid ';
   SET @sql = @sql + 'WHERE b.bomtype = 1 ';
   SET @sql = @sql + 'AND bp.parentid NOT IN (SELECT partid FROM ' + @erpdbname + 'bas_part WHERE invcode IN (SELECT cinvcode FROM ' + @erpdbname + 'inventory WHERE bself = 0 AND bpurchase = 1)) ';
   SET @sql = @sql + 'ORDER BY bc.sortseq, bc.opseq; ';
   EXEC(@sql);

   --创建临时表
   CREATE TABLE #temp_bom_parent
   (
            Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
            ParentId INT NULL,
            BomId INT NULL
   );

   --有后续子件的，获取最新版本的BOM
   SET @sql = '';
   SET @sql = @sql + 'INSERT INTO #temp_bom_parent(parentid, bomid) ';
   SET @sql = @sql + 'SELECT t.childid, b.bomid ';
   SET @sql = @sql + 'FROM (SELECT childid FROM #temp WHERE bomlevel = ' + CAST(@bomlevel+1 AS NVARCHAR) + ') t ';
   SET @sql = @sql + 'INNER JOIN ' + @erpdbname + 'bom_parent bp ';
   SET @sql = @sql + 'ON t.childid = bp.parentid ';
   SET @sql = @sql + 'INNER JOIN ' + @erpdbname + 'bom_bom b ';
   SET @sql = @sql + 'ON bp.bomid = b.bomid ';
   SET @sql = @sql + 'INNER JOIN ' + @erpdbname + ' bas_part bsp ';
   SET @sql = @sql + 'ON bp.parentid = bsp.partid ';
   SET @sql = @sql + 'INNER JOIN ' + @erpdbname + 'inventory inv on bsp.invcode = inv.cinvcode ';
   SET @sql = @sql + 'WHEREb.bomtype = 1 ';
   SET @sql = @sql + 'AND inv.bself = 1 ';
SET @sql = @sql + 'AND NOT EXISTS ( ';
   SET @sql = @sql + ' SELECT * ';
   SET @sql = @sql + ' FROM ' + @erpdbname + 'bom_bom b2 ';
   SET @sql = @sql + ' INNER JOIN ' + @erpdbname + 'bom_parent bp2 ';
   SET @sql = @sql + ' ON b2.bomid = bp2.bomid ';
   SET @sql = @sql + ' AND bp2.parentid = bp.parentid ';
   SET @sql = @sql + ' WHEREb2.bomtype = 1 ';
   SET @sql = @sql + ' AND bp2.parentid NOT IN (SELECT partid FROM ' + @erpdbname + 'bas_part WHERE invcode IN (SELECT cinvcode FROM ' + @erpdbname + 'inventory WHERE bself = 0 AND bpurchase = 1)) ';
   SET @sql = @sql + ' AND b2.versioneffdate > b.versioneffdate ';
   SET @sql = @sql + '); ';
   EXEC(@sql);

   --更新最新的BOMID
   UPDATE #temp
   SET childbomid = tbp.bomid
   FROM #temp_bom_parent tbp
   WHERE bomlevel = @bomlevel+1
   AND #temp.childid = tbp.parentid;

   DROP TABLE #temp_bom_parent;

   --计数决定是否完成展BOM
   SELECT @count = COUNT(*)
   FROM #temp
   WHERE bomlevel = @bomlevel+1 ;

   SET @bomlevel = @bomlevel + 1;

END;

--更新父件的物料信息
SET @sql = '';
SET @sql = @sql + 'UPDATE #temp ';
SET @sql = @sql + 'SET parentinvcode = bp.invcode, parentinvname = i.cinvname ';
SET @sql = @sql + 'FROM ' + @erpdbname + 'bas_part bp ';
SET @sql = @sql + 'INNER JOIN ' + @erpdbname + 'inventory i ';
SET @sql = @sql + 'ON bp.invcode = i.cinvcode ';
SET @sql = @sql + 'WHERE #temp.parentid = bp.partid; ';
EXEC(@sql);

--更新子件的物料信息
SET @sql = '';
SET @sql = @sql + 'UPDATE #temp ';
SET @sql = @sql + 'SET childinvcode = bp.invcode, childinvname = i.cinvname, ChildInvCCode = i.cinvccode,childinvstd = i.cinvstd, ';
SET @sql = @sql + 'childcomunitcode = i.ccomunitcode, childbself = i.bself, childbpurchase = i.bpurchase, childbproxyforeign = i.bproxyforeign ';
SET @sql = @sql + 'FROM ' + @erpdbname + 'bas_part bp ';
SET @sql = @sql + 'INNER JOIN ' + @erpdbname + 'inventory i ';
SET @sql = @sql + 'ON bp.invcode = i.cinvcode ';
SET @sql = @sql + 'WHERE #temp.childid = bp.partid; ';
EXEC(@sql);

--计算数量
UPDATE #temp
SET qty = @qty * qty * baseqtyncalc / baseqtydcalc;

SELECT *
FROM #temp
whereisnull(ChildBomId,'') = ''
ORDER BY bomlevel, id;

DROP TABLE #temp;

GO

yaoxiaoxiang 发表于 2017-4-7 16:33:56

不需要那么复杂，SQL2005版本的其实写起来比较简单。
我有导入和导出BOM的工具，

明珠求瑕 发表于 2017-4-10 09:00:10

我靠，也是人才啊

a12345611 发表于 2017-7-29 10:19:59

yaoxiaoxiang 发表于 2017-4-7 16:33
不需要那么复杂，SQL2005版本的其实写起来比较简单。
我有导入和导出BOM的工具，

BOM 导出工具可以共享吗

用友—吴桐 发表于 2018-6-3 18:31:58

yaoxiaoxiang 发表于 2017-4-7 16:33
不需要那么复杂，SQL2005版本的其实写起来比较简单。
我有导入和导出BOM的工具，

工具在哪里？

win_tang 发表于 2023-4-14 10:34:55

多谢分享，学习了

rollbin 发表于 2023-4-14 11:59:22

多谢分享！

fishboycn 发表于 2024-1-15 09:51:39

多谢。收藏一下

lsbenet 发表于 2024-2-21 11:42:11

多谢。收藏一下

页: [1]

用友之家-用友软件论坛's Archiver

U8 存储过程展开BOM表