易飞9.0ERP一次展多阶BOM清单(含采购单价).txt

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

SELECT @ITEM_LEVELNO = 1

INSERT INTO #VIEW1 (V1001, V1002, V1004, V1005, V1011, V1012, V1013)
SELECT MD003, @ITEMNO, MD002, 1, MD006 , MD007*MC004, MD008 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)
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