易飞8无销售报价的BOM成本参考

ALTER PROCEDURE [dbo].[SP_BOM_EXPAND] (@ITEMNO VARCHAR(80))
AS   
BEGIN
    --BOM展阶 
 --@ITEMNO：查询品号
 --返回:元件品号V1001，主件品号V1002，阶码V1004，阶层次V1005，尾阶标志V1006，展开标志V1007，组成用量V1011，底数V1012，损耗率%V1013,价格%V1014
 DECLARE @ITEM_CHILD VARCHAR(80), @ITEM_EXPAND VARCHAR(80), @ITEM_LEVELNO INT, @COUNT INT
 DECLARE @BATCH_NUMBER NUMERIC(18,6)
 DECLARE @JE NUMERIC(18,6)
DECLARE @JE2 NUMERIC(18,6)
DECLARE @PH VARCHAR(80)
DECLARE @SX VARCHAR(2)
DECLARE @rq varchar(8)
 CREATE TABLE  #VIEW1 (V1001 VARCHAR(80),VRQ VARCHAR(8), V1002 VARCHAR(80), 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 NUMERIC(18,6) DEFAULT 0) 


 SELECT @ITEM_LEVELNO = 1   --当前阶层为1


 INSERT INTO #VIEW1 (V1001, V1002, V1004, V1005, V1011, V1012, V1013,V1014)
 SELECT MD003, @ITEMNO, MD002, 1, MD006 , MD007*MC004, MD008,0 FROM BOMMC, BOMMD WHERE BOMMC.MC001 = BOMMD.MD001 AND MC001 = @ITEMNO
  --找一下输入参数的品号有没有相关的BOM单身档,看一下第一层
--select * from #VIEW1


 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, @ITEM_EXPAND
SET @SX=(SELECT MB025 FROM INVMB WHERE MB001=@ITEM_CHILD)
if(@SX='P')
begin
SET @JE=(select TOP 1 TH018 FROM PURTH WHERE TH004=@ITEM_CHILD ORDER BY TH014 DESC)
Set @rq=(select top 1 TH014 FROM PURTH WHERE TH004=@ITEM_CHILD ORDER BY TH014 DESC)
--SET @JE=(select TOP 1 LA012 FROM INVLA WHERE LA001=@ITEM_CHILD ORDER BY LA004 DESC)
--set @rq=(select top 1 LA004 FROM INVLA  WHERE LA001=@ITEM_CHILD ORDER BY LA004 DESC)
END
ELSE IF(@SX='S')
SET @JE=(select TOP 1 TA022 from MOCTA where TA006=@ITEM_CHILD order by  TA003 DESC)
ELSE IF(@SX='M' OR @SX='Y' OR @SX='C')
-- SET @JE=0
if(@ITEM_LEVELNO=1)
set @JE=(select TOP 1 LB012+LB013 
from INVLB 
WHERE LB012<>0 AND LB013 <>0 and LB001=@ITEM_CHILD ORDER BY LB002 DESC)
else 
SET @JE=0



UPDATE #VIEW1 SET V1007 = '1' WHERE CURRENT OF BOMEXPAND --将第一层的展开标致为1,更新了游标锁定的表


SET @JE2=(SELECT MB057 FROM INVMB WHERE MB001=@ITEM_CHILD)
set @JE=ISNULL(@JE,0)
IF(@JE=0 AND @SX='P')
UPDATE #VIEW1 SET V1014=@JE2,VRQ='' WHERE CURRENT OF BOMEXPAND
ELSE
begin
UPDATE #VIEW1 SET V1014=@JE,VRQ=@rq where CURRENT OF BOMEXPAND
--UPDATE #VIEW1 SET Vrq=@rq where CURRENT OF BOMEXPAND
end
SELECT @COUNT = COUNT(*) FROM BOMMC, BOMMD WHERE BOMMC.MC001 = BOMMD.MD001 AND MC001 = @ITEM_CHILD  --统计一下BOMMD单身档里有多少条
IF @COUNT = 0 
UPDATE #VIEW1 SET V1006 = '1' WHERE CURRENT OF BOMEXPAND   --如果没有了，则当前一阶即为尾阶,更新了游标锁定的表
ELSE
BEGIN
INSERT INTO #VIEW1 (V1001, V1002, V1004, V1005, V1011, V1012, V1013)
SELECT MD003, @ITEM_CHILD, @ITEM_EXPAND + '.' + MD002, @ITEM_LEVELNO + 1, MD006 * @BATCH_NUMBER, MD007*MC004, MD008 FROM BOMMC, BOMMD WHERE BOMMC.MC001 = BOMMD.MD001 AND MC001 = @ITEM_CHILD
--插入当前扫描的行的子项进入到表里去,同时因为游标有了FOR UPDATE，还会进入循环


--SET @JE=(select TOP 1 TH018 FROM PURTH WHERE TH004=@ITEM_CHILD ORDER BY TH014 DESC)
--UPDATE #VIEW1 SET V1014=@JE WHERE V1001=@ITEM_CHILD
END
FETCH NEXT FROM BOMEXPAND INTO @ITEM_CHILD, @ITEM_EXPAND, @ITEM_LEVELNO, @BATCH_NUMBER /* 在循环体内将读取其余行数据 */
 END
 CLOSE BOMEXPAND   /* 关闭游标 */ 
 DEALLOCATE BOMEXPAND   /* 删除游标 */ 
--SELECT * FROM #VIEW1
--
--UNION
  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  品号属性,VRQ AS '采购日期',
         MB002 品名,MB003 规格,MB004 as 单位,V1011 as 组成用量,V1012 as 底数,V1013 as 损耗率,V1011/V1012*(1+V1013) as 总用量,V1014 AS 单价, V1011/V1012*(1+V1013)*V1014 AS 总价 FROM #VIEW1 INNER JOIN  INVMB ON V1001=MB001
--union all
--select '',  '', '', '' , '' ,'',  '', '', '' , NULL ,NULL,NULL, SUM(V1011/V1012*(1+V1013)*V1014)  from #VIEW1
ORDER BY V1004


SELECT @ITEMNO 主件品号, SUM(V1011/V1012*(1+V1013)*V1014) as 合计材料进价 FROM #VIEW1







END