82 lines
2.7 KiB
Transact-SQL
82 lines
2.7 KiB
Transact-SQL
-- 物料入库组件自制件入库模块添加入库单按钮配套测试库脚本
|
|
-- 修改人:Ld 修改时间:2026-07-14 09:05:57;
|
|
|
|
ALTER PROCEDURE [dbo].[仓储管理_入库单_查询]
|
|
@类型_check bit = 0,
|
|
@类型 nvarchar(50) = null
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
-- 修改人:Ld 修改时间:2026-07-14 09:05:57; 返回订单和类型字段，并支持采购/自制件入库单筛选
|
|
SELECT
|
|
入库单.入库单流水号,
|
|
入库单.入库单号,
|
|
入库单.采购合同流水号,
|
|
入库单.采购合同号,
|
|
供应商.供应商名称,
|
|
供应商.供应商流水号,
|
|
入库单.到货数,
|
|
入库单.是否入库,
|
|
入库单.入库时间,
|
|
入库单.订单流水号,
|
|
入库单.订单号,
|
|
入库单.类型
|
|
FROM dbo.仓储管理_入库单 AS 入库单
|
|
LEFT JOIN dbo.采购管理_供应商 AS 供应商
|
|
ON 入库单.供应商流水号 = 供应商.供应商流水号
|
|
WHERE
|
|
ISNULL(@类型_check, 0) = 0
|
|
OR (
|
|
@类型 = N'自制'
|
|
AND ISNULL(入库单.类型, N'') = N'自制'
|
|
)
|
|
OR (
|
|
@类型 = N'采购'
|
|
AND ISNULL(入库单.类型, N'') <> N'自制'
|
|
);
|
|
END
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.trg_库存管理_自制件_入库记录_回写入库单', N'TR') IS NOT NULL
|
|
DROP TRIGGER dbo.trg_库存管理_自制件_入库记录_回写入库单;
|
|
GO
|
|
|
|
CREATE TRIGGER dbo.trg_库存管理_自制件_入库记录_回写入库单
|
|
ON dbo.库存管理_自制件_入库记录
|
|
AFTER INSERT
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
-- 修改人:Ld 修改时间:2026-07-14 09:05:57; 自制件完成入库后按订单回写最近一张待入库单状态
|
|
;WITH 候选入库单 AS (
|
|
SELECT
|
|
入库单.入库单流水号,
|
|
ROW_NUMBER() OVER (
|
|
PARTITION BY 新记录.入库记录流水号
|
|
ORDER BY
|
|
CASE WHEN CONVERT(float, ISNULL(入库单.到货数, 0)) = CONVERT(float, ISNULL(新记录.实际到货数量, 0)) THEN 0 ELSE 1 END,
|
|
入库单.入库单流水号
|
|
) AS 排序号
|
|
FROM inserted AS 新记录
|
|
INNER JOIN dbo.车间生产管理_零件工艺计划_视图 AS 工艺计划
|
|
ON 新记录.工艺计划流水号 = 工艺计划.工艺计划流水号
|
|
INNER JOIN dbo.仓储管理_入库单 AS 入库单
|
|
ON 入库单.订单流水号 = CONVERT(nvarchar(100), 工艺计划.订单流水号)
|
|
AND ISNULL(入库单.类型, N'') = N'自制'
|
|
AND ISNULL(入库单.是否入库, 0) = 0
|
|
)
|
|
UPDATE 入库单
|
|
SET
|
|
入库单.是否入库 = 1,
|
|
入库单.入库时间 = GETDATE()
|
|
FROM dbo.仓储管理_入库单 AS 入库单
|
|
INNER JOIN 候选入库单 AS 候选
|
|
ON 入库单.入库单流水号 = 候选.入库单流水号
|
|
WHERE 候选.排序号 = 1;
|
|
END
|
|
GO
|
|
|
|
-- 修改人:Ld 修改时间:2026-07-14 09:05:57;
|