[易飞]开立工单自动带出BOM中的插件位置

/* *用途: 开立工单时候自动带出插件位置 *作者：龚德辉 2011-03-14 */ CREATE TRIGGER [H_TR_MOCTB] ON [dbo].[MOCTB] FOR INSERT AS declare @ITEMNO VARCHAR(20) BEGIN TRANSACTION SET NOCOUNT ON SELECT @ITEMNO=TA006 FROM MOCTA Inner Join Inserted on TA001=TB001 AND TA002=TB002 --BOM展阶 [email protected]：查询品号 --返回:元件品号V1001，主件品号V1002，阶码V1004，阶层次V1005，尾阶标志V1006，展开标志V1007，组成用量V1011，底数V1012，损耗率%V1013 DECLARE @ITEM_CHILD VARCHAR(20), @ITEM_EXPAND VARCHAR(20), @ITEM_LEVELNO INT, @COUNT INT DECLARE @BATCH_NUMBER NUMERIC(18,6) CREATE TABLE #VIEW1 (V1001 VARCHAR(20), V1002 VARCHAR(20), V1004 VARCHAR(80) DEFAULT '', V1005 INT DEFAULT 0, V1006 CHAR(1) DEFAULT '0', V1007 CHAR(1) DEFAULT '0' , V1011 NUMERIC(18,6) DEFAULT 0, V1012 NUMERIC(18,6) DEFAULT 0, V1013 NUMERIC(18,6) DEFAULT 0,V1014 VARCHAR(255)) SELECT @ITEM_LEVELNO = 1 INSERT INTO #VIEW1 (V1001, V1002, V1004, V1005, V1011, V1012, V1013,V1014) SELECT MD003, @ITEMNO, MD002, 1, MD006 , MD007*MC004, MD008,MD019 FROM BOMMC, BOMMD WHERE BOMMC.MC001 = BOMMD.MD001 AND MC001 = @ITEMNO DECLARE BOMEXPAND CURSOR FOR SELECT V1001, V1004, V1005, V1011/V1012 FROM #VIEW1 WHERE V1007 = '0' FOR UPDATE OPEN BOMEXPAND /* 打开游标 */ FETCH NEXT FROM BOMEXPAND INTO @ITEM_CHILD, @ITEM_EXPAND, @ITEM_LEVELNO, @BATCH_NUMBER WHILE @@FETCH_STATUS = 0 /* 用WHILE循环控制游标活动*/ BEGIN --SELECT @ITEM_LEVELNO, @ITEM_CHILD UPDATE #VIEW1 SET V1007 = '1' WHERE CURRENT OF BOMEXPAND SELECT @COUNT = COUNT(*) FROM BOMMC, BOMMD WHERE BOMMC.MC001 = BOMMD.MD001 AND MC001 = @ITEM_CHILD IF @COUNT = 0 UPDATE #VIEW1 SET V1006 = '1' WHERE CURRENT OF BOMEXPAND ELSE BEGIN INSERT INTO #VIEW1 (V1001, V1002, V1004, V1005, V1011, V1012, V1013,V1014) SELECT MD003, @ITEM_CHILD, @ITEM_EXPAND + '.' + MD002, @ITEM_LEVELNO + 1, MD006 * @BATCH_NUMBER, MD007*MC004, MD008,MD019 FROM BOMMC, BOMMD WHERE BOMMC.MC001 = BOMMD.MD001 AND MC001 = @ITEM_CHILD END FETCH NEXT FROM BOMEXPAND INTO @ITEM_CHILD, @ITEM_EXPAND, @ITEM_LEVELNO, @BATCH_NUMBER /* 在循环体内将读取其余行数据 */ END CLOSE BOMEXPAND /* 关闭游标 */ DEALLOCATE BOMEXPAND /* 删除游标 */ /* SELECT space(V1005*1)+'.'+cast(V1005 as varchar) as 阶层,V1002 as 主件品号,V1001 as 元件品号,case when MB025='P' THEN '采购件' when MB025='M' THEN '自制件' when MB025='S' THEN '委外加工件' when MB025='Y' THEN '虚设件' else '配置件'END AS 品号属性, MB002 品名,MB003 规格,MB004 as 单位,V1011 as 组成用量,V1012 as 底数,V1013 as 损耗率,V1011/V1012*(1+V1013) as 累计组成用量,V1014 插件位置1 FROM #VIEW1 INNER JOIN INVMB ON V1001=MB001 ORDER BY V1004 */ UPDATE t1 set t1.TB017=t3.V1014 FROM MOCTB t1 Inner Join Inserted t2 on t1.TB001=t2.TB001 And t1.TB002=t2.TB002 And t1.TB003=t2.TB003 Inner Join #VIEW1 t3 on t3.V1001=t1.TB003 AND t3.V1002=t1.TB014 drop table #VIEW1 SET NOCOUNT OFF COMMIT TRANSACTION