物料需求计算存储过程设计

ALTER PROCEDURE [dbo].[X9_XQJS]
    @DQYH  NVARCHAR(10),  --用户名称
    @DJOID VARCHAR(30)    --销售订单OID
AS
BEGIN TRY
  SET NOCOUNT ON;
  DECLARE @DQJC INT;
  DECLARE @DQNY CHAR(6);
  DECLARE @RKERR AS VARCHAR(200);
  IF NOT EXISTS(SELECT SHR FROM DJCHO WHERE DJMC='销售订单' AND OID=@DJOID AND SHR<>'' AND DJR<>'') 
    BEGIN
      SELECT @RKERR='单据['+@DJOID+']不存在或未登记！' ;
      THROW 50007,@RKERR,7
    END
  IF EXISTS(SELECT 1 FROM DJCHM DD INNER JOIN MLQD QD ON QD.CPCHXH=DD.CHXH 
      WHERE DD.OID=@DJOID AND QD.SFSH=0) 
    BEGIN
      SELECT @RKERR='单据['+@DJOID+']的物料清单未被审核,无法进行需求计算' ;
      THROW 50007,@RKERR,7
    END
  --   步骤 
  -- 1.删除原有计算结果
  DELETE FROM UTWLXQ WHERE OID=@DJOID;  
  SET @DQNY=DBO.X9_DQNY();
  -- 2.生成当前单据的一级物料需求行
  INSERT INTO UTWLXQ   (OID,NY,CPCHXH,JGDH,SJCHXH,CLCHXH,JC,CLLB,XQSL)
    SELECT @DJOID AS OID,@DQNY AS NY,MLQD.CPCHXH, MLQD.JGDH, '' AS SJCHXH,MLQD.CPCHXH, MLQD.JC, 
        MLQD.CLLB,  SUM(ROUND((DJCHM.XQSL * MLQD.DESL) * (1 + MLQD.DESH / 100), MLQD.JSJD)) AS XQSL 
      FROM DJCHM INNER JOIN  MLQD ON DJCHM.CHXH = MLQD.CPCHXH
      GROUP BY DJCHM.OID,MLQD.CPCHXH,MLQD.JGDH,MLQD.CHXH,MLQD.JC,MLQD.CLLB 
      HAVING      (DJCHM.OID = @DJOID) AND (MLQD.JC = 1);
  -- 3.分级计算
  SET @DQJC=1;
  WHILE (@DQJC<=6)
    BEGIN
      --生成下一级物料需求
      INSERT INTO UTWLXQ (OID,NY,CPCHXH, JGDH, SJCHXH,CLCHXH, JC, CLLB,XQSL)
        SELECT @DJOID AS OID,@DQNY AS NY, MLQD.CPCHXH, MLQD.JGDH,WLXQ.CLCHXH AS SJCHXH,MLQD.CHXH, MLQD.JC,
            MLQD.CLLB, SUM(ROUND((WLXQ.XQSL * MLQD.DESL) * (1 + MLQD.DESH / 100), MLQD.JSJD)) AS XQSL 
        FROM  UTWLXQ AS WLXQ INNER JOIN MLQD ON WLXQ.CPCHXH = MLQD.CPCHXH AND MLQD.JGDH LIKE WLXQ.JGDH + '%'
        WHERE    (WLXQ.OID = @DJOID) AND (WLXQ.JC = @DQJC) AND (WLXQ.XQSL>0) AND (MLQD.JC = @DQJC + 1)
          GROUP BY MLQD.CPCHXH, MLQD.JGDH,WLXQ.CLCHXH,MLQD.CHXH,MLQD.JC,MLQD.CLLB;
      SET @DQJC=@DQJC+1;
    END
END TRY
BEGIN CATCH
    THROW;
END CATCH;