-- -- 库存分配系统完整实现 (SQL Server优化版)
-- WITH
-- -- 步骤1: 获取所有未清订单行
-- OrderLines AS (
-- SELECT
-- COALESCE(o.NumAtCard, a.U_PurContNo) AS 采购合同号,
-- a.DocEntry,
-- a.linenum,
-- a.itemcode,
-- a.DocDate,
-- a.quantity AS 订单数量,
-- COALESCE(b.交货数量, 0) AS 交货数量,
-- a.quantity - COALESCE(b.交货数量, 0) AS 未清数量,
-- o.U_company,
-- a.U_Brand
-- FROM RDR1 a
-- LEFT JOIN OUDP d ON a.U_Department = d.Name
-- LEFT JOIN (
-- SELECT
-- T.OR_Entry,
-- T.OR_Line,
-- T.物料编码,
-- SUM(T.交货数量) AS 交货数量
-- FROM [ZY_TB_Sales_Details] T
-- WHERE T.业务体系 = '经销体系'
-- GROUP BY T.OR_Entry, T.OR_Line, T.物料编码
-- ) b ON a.DocEntry = b.OR_Entry AND a.linenum = b.OR_Line
-- LEFT JOIN ORDR o ON a.DocEntry = o.DocEntry
-- WHERE a.linestatus = 'o'
-- AND a.quantity - COALESCE(b.交货数量, 0) > 0
-- AND d.Remarks = '经销体系'
-- ),
--
-- -- 步骤2: 获取可用库存数据
-- Stock AS (
-- SELECT
-- 物料编号,
-- 批次编号,
-- LTRIM(RTRIM(公司)) AS 公司,
-- 效期,
-- 库存数量
-- FROM [ZY_TB_BI_Stock]
-- WHERE 备货占用类型 = '备货未占'
-- AND 业务体系 = '经销体系'
-- ),
--
-- -- 步骤3: 创建库存组映射
-- StockGroup AS (
-- SELECT
-- 物料编号,
-- 公司,
-- 批次编号,
-- 效期,
-- 库存数量,
-- CASE
-- WHEN 公司 IN ('广州佰路', '中原合聚') THEN 'CytivaGroup'
-- ELSE 公司
-- END AS 库存组
-- FROM Stock
-- ),
--
-- -- 步骤4: 计算库存批次累计量(FIFO基础) - 修复语法错误
-- StockCTE AS (
-- SELECT
-- 物料编号,
-- 批次编号,
-- 公司,
-- 效期,
-- 库存数量,
-- 库存组,
-- SUM(库存数量) OVER (
-- PARTITION BY 物料编号, 库存组
-- ORDER BY 效期, 批次编号
-- ROWS UNBOUNDED PRECEDING
-- ) AS 累计库存,
-- SUM(库存数量) OVER (
-- PARTITION BY 物料编号, 库存组
-- ORDER BY 效期, 批次编号
-- ROWS UNBOUNDED PRECEDING
-- ) - 库存数量 AS 前序累计库存
-- FROM StockGroup
-- ),
--
-- -- 步骤5: 创建订单组映射
-- OrderGroup AS (
-- SELECT
-- *,
-- CASE
-- WHEN U_Brand = 'Cytiva' OR U_company IN ('广州佰路', '中原合聚')
-- THEN 'CytivaGroup'
-- ELSE LTRIM(RTRIM(U_company))
-- END AS 需求组
-- FROM OrderLines
-- ),
--
-- -- 步骤6: 计算订单累计需求 - 修复语法错误
-- OrderLinesCTE AS (
-- SELECT
-- o.*,
-- COALESCE(st.总库存, 0) AS 当前在库存的数量,
-- SUM(o.未清数量) OVER (
-- PARTITION BY o.itemcode, o.需求组
-- ORDER BY o.DocDate, o.DocEntry, o.linenum
-- ROWS UNBOUNDED PRECEDING
-- ) AS 累计未清量,
-- ISNULL(
-- SUM(o.未清数量) OVER (
-- PARTITION BY o.itemcode, o.需求组
-- ORDER BY o.DocDate, o.DocEntry, o.linenum
-- ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
-- ),
-- 0
-- ) AS 前序累计未清量
-- FROM OrderGroup o
-- LEFT JOIN (
-- SELECT 物料编号, 库存组, SUM(库存数量) AS 总库存
-- FROM StockGroup
-- GROUP BY 物料编号, 库存组
-- ) st ON o.itemcode = st.物料编号 AND o.需求组 = st.库存组
-- ),
--
-- -- 步骤7: 执行库存分配逻辑(FIFO规则)
-- Allocation AS (
-- SELECT
-- o.采购合同号,
-- o.DocEntry,
-- o.linenum,
-- o.itemcode,
-- o.DocDate,
-- o.订单数量,
-- o.交货数量,
-- o.未清数量,
-- o.当前在库存的数量,
-- o.U_Company,
-- o.U_Brand,
-- s.批次编号,
-- s.公司,
-- s.效期,
-- s.库存数量 AS 批次库存,
-- -- 四重约束分配逻辑 (兼容SQL Server)
-- CASE
-- WHEN o.累计未清量 > s.前序累计库存
-- AND o.前序累计未清量 < s.累计库存
-- THEN
-- (SELECT MIN(V)
-- FROM (VALUES
-- (s.库存数量),
-- (o.未清数量),
-- (s.累计库存 - o.前序累计未清量),
-- (o.累计未清量 - s.前序累计库存)
-- ) AS T(V))
-- ELSE 0
-- END AS 分配数量
-- FROM OrderLinesCTE o
-- JOIN StockCTE s
-- ON o.itemcode = s.物料编号
-- AND o.需求组 = s.库存组
-- WHERE o.累计未清量 > s.前序累计库存
-- AND o.前序累计未清量 < s.累计库存
-- )
--
-- -- 步骤8: 输出最终分配结果
-- SELECT
-- 采购合同号,
-- DocEntry AS 订单号,
-- linenum AS 行号,
-- itemcode AS 物料编码,
-- DocDate AS 单据日期,
-- U_Company AS 订单所属公司,
-- U_Brand AS 产品线,
-- 订单数量,
-- 交货数量,
-- 未清数量,
-- 当前在库存的数量,
-- 批次编号,
-- 公司 AS 库存所属公司,
-- 效期,
-- 分配数量,
-- SUM(分配数量) OVER (
-- PARTITION BY DocEntry, linenum, itemcode
-- ) AS 该行可分配数量,
-- 未清数量 - SUM(分配数量) OVER (
-- PARTITION BY DocEntry, linenum, itemcode
-- ) AS 缺少数量
-- FROM Allocation
-- ORDER BY
-- DocDate DESC,
-- DocEntry,
-- linenum,
-- itemcode,
-- 效期;
--
优化这个代码 增加分配状态列 并且无论是否可以分配到货 都展示出来
最新发布