基于替代产品的采购算法开发及库存更新技术问询

一、核心业务规则明确 #

针对minimum_target为0的产品，执行以下采购逻辑：

• 禁止直接采购该产品本身
• 优先用其替代产品的冗余库存（即amount_in_hand - minimum_target的正值部分）填补purchasing_request缺口
• 若冗余库存不足以覆盖缺口，剩余部分向首个有效替代产品生成采购请求

二、样例表定义（DDL/DML） #

-- 自定义结果表结构
CREATE TABLE product_purchase_data (
    product_code VARCHAR(20) PRIMARY KEY,
    alt_code_1 VARCHAR(20),
    alt_code_2 VARCHAR(20),
    alt_code_3 VARCHAR(20),
    alt_code_4 VARCHAR(20),
    minimum_target DECIMAL(10,2),
    amount_in_hand DECIMAL(10,2),
    purchasing_request DECIMAL(10,2)
);

-- 测试数据插入
INSERT INTO product_purchase_data VALUES
('PROD001', 'ALT001', 'ALT002', NULL, NULL, 0, 50, 200),
('ALT001', NULL, NULL, NULL, NULL, 100, 180, 0),
('ALT002', NULL, NULL, NULL, NULL, 80, 90, 0);

三、合规视图生成SQL #

CREATE VIEW vw_compliant_purchase AS
WITH product_alt_mapping AS (
    -- 提取产品及首个有效替代编码
    SELECT 
        p.product_code,
        COALESCE(p.alt_code_1, p.alt_code_2, p.alt_code_3, p.alt_code_4) AS primary_alt_code,
        p.minimum_target,
        p.amount_in_hand,
        p.purchasing_request
    FROM product_purchase_data p
),
alt_redundant_calc AS (
    -- 计算替代产品的可用冗余库存
    SELECT 
        pam.product_code,
        pam.primary_alt_code,
        pam.purchasing_request,
        GREATEST(a.amount_in_hand - a.minimum_target, 0) AS alt_redundant_stock
    FROM product_alt_mapping pam
    LEFT JOIN product_purchase_data a ON pam.primary_alt_code = a.product_code
)
-- 处理min_target为0的产品
SELECT 
    product_code,
    primary_alt_code AS purchase_target_code,
    purchasing_request AS original_request,
    alt_redundant_stock,
    GREATEST(purchasing_request - alt_redundant_stock, 0) AS final_purchase_qty,
    CASE WHEN alt_redundant_stock > 0 THEN 'Y' ELSE 'N' END AS used_redundant_stock
FROM alt_redundant_calc
WHERE minimum_target = 0
UNION ALL
-- 处理min_target不为0的产品（直接采购自身）
SELECT 
    product_code,
    product_code AS purchase_target_code,
    purchasing_request AS original_request,
    0 AS alt_redundant_stock,
    purchasing_request AS final_purchase_qty,
    'N' AS used_redundant_stock
FROM product_purchase_data
WHERE minimum_target > 0;

四、存储过程修改思路（更新amount_in_hand） #

由于无法获取原存储过程代码，以下是针对性的修改逻辑框架：

核心修改步骤 #

1. 在原存储过程的临时表数据写入自定义表步骤之后，新增库存更新逻辑
2. 先计算并记录被使用的替代产品冗余库存数量
3. 批量更新对应替代产品的amount_in_hand
4. 若生成了新的采购请求，需在后续采购入库环节同步更新目标产品的库存

样例逻辑代码片段 #

-- 假设原存储过程已完成临时表到product_purchase_data的数据写入
-- 1. 临时存储冗余库存使用量
CREATE TABLE #temp_stock_consume (
    alt_product_code VARCHAR(20),
    consumed_qty DECIMAL(10,2)
);

INSERT INTO #temp_stock_consume
SELECT 
    primary_alt_code,
    LEAST(purchasing_request, alt_redundant_stock)
FROM vw_compliant_purchase
WHERE used_redundant_stock = 'Y';

-- 2. 更新替代产品的在手库存
UPDATE target
SET target.amount_in_hand = target.amount_in_hand - consume.consumed_qty
FROM product_purchase_data target
JOIN #temp_stock_consume consume ON target.product_code = consume.alt_product_code;

-- 3. 清理临时表
DROP TABLE #temp_stock_consume;

关键注意事项 #

• 必须添加事务包裹，避免数据写入与库存更新不一致
• 若存在并发执行场景，需添加行级锁或乐观锁机制防止冲突
• 建议新增库存变更日志表，记录每一次库存调整的明细，便于追溯

内容的提问来源于stack exchange，提问作者dnz07