原创

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

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://david.blog.csdn.net/article/details/6248389
/*
 *用途: 开立工单时候自动带出插件位置
 *作者:龚德辉 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展阶 
 --@ITEMNO:查询品号
 --返回:元件品号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
文章最后发布于: 2011-03-14 15:16:00
展开阅读全文
0 个人打赏
私信求帮助

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 鲸 设计师: meimeiellie

分享到微信朋友圈

×

扫一扫,手机浏览