库存BOM分解表值函数

CREATE OR ALTER FUNCTION dbo.库存BOM分解表值函数()
RETURNS @Result TABLE (
单号 NVARCHAR(100),
深度 INT,
层级 NVARCHAR(100),
产品编码 NVARCHAR(100),
数量 DECIMAL(20, 4),
上级编码 NVARCHAR(100),
上级数量 DECIMAL(20, 4),
上级组序 DECIMAL(10, 2),
本级库存量 DECIMAL(20, 4),
上级库存量 DECIMAL(20, 4),
累计库存量 DECIMAL(20, 4),
组序 INT,
组内序号 INT,
分组标识 NVARCHAR(500)
)
AS
BEGIN
WITH LevelData AS
(SELECT F111684 AS 单号, F111685 AS 深度, F111686 AS 层级, F111687 AS 产品编码, F111688 AS 数量,
F112017 AS 本级库存量,
/ 提取父级层级/
CASE WHEN F111686 = '0' THEN NULL
WHEN CHARINDEX('.', F111686) = 0 THEN '0'
ELSE LEFT(F111686, LEN(F111686) - CHARINDEX('.', REVERSE(F111686)))
END AS 父级层级,
F111686 AS 完整层级
FROM tabDIYTable12734
),
ParentInfo AS
(SELECT child.单号, child.深度, child.层级, child.产品编码, child.数量,
child.本级库存量,
child.完整层级,
parent.产品编码 AS 上级编码,
parent.数量 AS 上级数量,
parent.完整层级 AS 上级完整层级
FROM LevelData child
LEFT JOIN LevelData parent
ON child.单号 = parent.单号
AND child.父级层级 = parent.层级
),
GroupCalculation AS
(SELECT pi.,
/ 改进的分组键：包含完整的来源信息/
CASE WHEN pi.上级编码 IS NULL
THEN 'ROOT'
/ 对于同一上级的不同出现，使用完整路径区分/
ELSE pi.上级编码 + '|' +
CASE WHEN CHARINDEX('.', pi.完整层级) = 0
THEN pi.完整层级
/ 提取到当前层级的完整前缀/
ELSE LEFT(pi.完整层级,
CASE WHEN CHARINDEX('.', pi.完整层级, CHARINDEX('.', pi.完整层级) + 1) > 0
THEN CHARINDEX('.', pi.完整层级, CHARINDEX('.', pi.完整层级) + 1) - 1
ELSE LEN(pi.完整层级)
END)
END
END AS 分组键,
/ 上级组序：从上级完整路径提取/
CAST(CASE WHEN pi.上级完整层级 IS NULL THEN NULL
WHEN CHARINDEX('.', pi.上级完整层级) = 0
THEN CAST(pi.上级完整层级 AS DECIMAL(10, 2))
ELSE CAST(RIGHT(pi.上级完整层级, CHARINDEX('.', REVERSE(pi.上级完整层级)) - 1) AS DECIMAL(10, 2))
END AS DECIMAL(10, 2)) AS 上级组序
FROM ParentInfo pi
),
RecursiveInventory AS
(SELECT gc.单号, gc.深度, gc.层级, gc.产品编码, gc.数量,
gc.上级编码, gc.上级数量, gc.上级组序,
gc.本级库存量,
/ 上级库存量：根节点没有上级库存量 /
CAST(NULL AS DECIMAL(20, 4)) AS 上级库存量,
/ 累计库存量：根节点 = 本级库存量 */
CAST(gc.本级库存量 AS DECIMAL(20, 4)) AS 累计库存量,
gc.分组键,
gc.完整层级,
gc.上级完整层级
FROM GroupCalculation gc
WHERE gc.上级编码 IS NULL

     UNION ALL

     SELECT  gc.单号, gc.深度, gc.层级, gc.产品编码, gc.数量, 
             gc.上级编码, gc.上级数量, gc.上级组序,
             gc.本级库存量,
             /* 上级库存量 = 上级累计库存量 × 本级数量 */
             CAST(ri.累计库存量 * gc.数量 AS DECIMAL(20, 4)) AS 上级库存量,
             /* 累计库存量 = 上级库存量 + 本级库存量 */
             CAST(ri.累计库存量 * gc.数量 + gc.本级库存量 AS DECIMAL(20, 4)) AS 累计库存量,
             gc.分组键,
             gc.完整层级,
             gc.上级完整层级
     FROM    GroupCalculation gc
     INNER JOIN RecursiveInventory ri 
             ON gc.单号 = ri.单号 
            AND gc.上级完整层级 = ri.完整层级
    )

-- 插入结果到返回表
INSERT INTO @Result
SELECT  ri.单号, ri.深度, ri.层级, ri.产品编码, ri.数量, 
        ri.上级编码, ri.上级数量, ri.上级组序,
        ri.本级库存量,
        ri.上级库存量,
        ri.累计库存量,
        /* 组序：确保同一上级下的不同路径分配到不同组*/ 
        DENSE_RANK() OVER (PARTITION BY ri.单号, ri.上级编码 ORDER BY ri.分组键) AS 组序, 
        /* 组内序号*/ 
        ROW_NUMBER() OVER (PARTITION BY ri.单号, ri.上级编码, ri.分组键 ORDER BY ri.层级, ri.产品编码) AS 组内序号, 
        ri.分组键 AS 分组标识
FROM    RecursiveInventory ri
ORDER BY ri.单号, ri.完整层级;

RETURN;

END;