22,207
社区成员
发帖
与我相关
我的任务
分享
--WMS_StockTemporarily 库存表
--WMS_Out_OrderListPicking 发货表
--目前是按照两个表SkuCode,BatchNumber,SKuStatus 这三个字段完全相同才进行更新WMS_StockTemporarily 表ZTotalQuantity字段
--问题:如果WMS_Out_OrderListPicking 表中BatchNumber中为空则按照SkuCode,SKuStatus 去匹配,
-- 如果BatchNumber不为空,则按照SkuCode,BatchNumber,SKuStatus去匹配(就是目前的方式)
--以下为执行SQL,表结构及数据
/*
执行sql
*/
;WITH a AS ( SELECT a.OrderBy_Id ,
Company_Guid ,
Warehouse_Guid ,
SkuCode ,
SkuStatus ,
BatchNumber ,
TotalQuantity ,
ZTotalQuantity ,
[库存_SUM] = ( SELECT SUM(TotalQuantity)
FROM WMS_StockTemporarily
WHERE Company_Guid = a.Company_Guid
AND Warehouse_Guid = a.Warehouse_Guid
AND SkuCode = a.SkuCode
AND BatchNumber = a.BatchNumber
AND SkuStatus = a.SkuStatus
AND OrderBy_Id <= a.OrderBy_Id
)
FROM dbo.WMS_StockTemporarily a
WHERE TotalQuantity > 0
),
b AS ( SELECT OrderNumber ,
SkuCode ,
SkuStatus ,
BatchNumber ,
SUM(TotalQuantity - b.PickingQuantity) AS OrderListQty ,
[出库_SUM] = ( SELECT SUM(TotalQuantity
- PickingQuantity)
FROM WMS_Out_OrderListPicking
WHERE SkuCode = b.SkuCode
AND BatchNumber = b.BatchNumber
AND SkuStatus = b.SkuStatus
)
FROM dbo.WMS_Out_OrderListPicking b
WHERE b.OrderNumber = 'OGR18030800002'
GROUP BY b.OrderNumber ,
b.SkuCode ,
b.SkuStatus ,
b.BatchNumber
)
UPDATE a
SET ZTotalQuantity = CASE WHEN b.[出库_SUM] < a.[库存_SUM] THEN b.[出库_SUM]
ELSE a.[库存_SUM]
END
- CASE WHEN b.[出库_SUM] - b.OrderListQty < a.[库存_SUM]
- a.TotalQuantity THEN a.[库存_SUM] - a.TotalQuantity
ELSE b.[出库_SUM] - b.OrderListQty
END
FROM a
JOIN b ON a.Company_Guid = '40F7A909-A3EC-4EFE-AE80-BB01CB4E7964'
AND a.Warehouse_Guid = '8CC42CD4-3885-4642-8CFB-FA004BEABD40'
AND a.SkuCode = b.SkuCode
AND a.BatchNumber = b.BatchNumber
AND a.SkuStatus = b.SkuStatus
AND b.OrderNumber = 'OGR18030800002'
AND b.[出库_SUM] - b.OrderListQty < a.[库存_SUM]
AND a.[库存_SUM] - a.TotalQuantity < b.[出库_SUM];
-- ----------------------------
-- Table structure for WMS_StockTemporarily
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[WMS_StockTemporarily]') AND type IN ('U'))
DROP TABLE [dbo].[WMS_StockTemporarily]
GO
CREATE TABLE [dbo].[WMS_StockTemporarily] (
[StockTemporarily_ID] bigint IDENTITY(1,1) NOT NULL,
[Stock_Id] bigint NOT NULL,
[Company_Guid] nvarchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Warehouse_Guid] nvarchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Stock_Date] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[OrderNumber] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[ERPNumber] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[ShelvesNumber] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[SkuCode] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[SkuName] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[SkuStatus] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[SkuUnit] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[BatchNumber] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[ProductionDate] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[ValidityDate] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[ShelvesLoca] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[LocaLevel] int NULL,
[TotalQuantity] bigint NULL,
[LTotalQuantity] bigint NULL,
[ZTotalQuantity] bigint NULL,
[STotalQuantity] bigint NULL,
[YTotalQuantity] bigint NULL,
[Remarks] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[CreateID] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[CreateDate] datetime NULL,
[customText1] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText2] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText3] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText4] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText5] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText6] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText7] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText8] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText9] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText10] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[OrderBy_Id] bigint NULL
)
GO
ALTER TABLE [dbo].[WMS_StockTemporarily] SET (LOCK_ESCALATION = TABLE)
GO
EXEC sp_addextendedproperty
'MS_Description', N'公司Guid',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'Company_Guid'
GO
EXEC sp_addextendedproperty
'MS_Description', N'仓库Guid',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'Warehouse_Guid'
GO
EXEC sp_addextendedproperty
'MS_Description', N'入库日期',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'Stock_Date'
GO
EXEC sp_addextendedproperty
'MS_Description', N'入库单号',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'OrderNumber'
GO
EXEC sp_addextendedproperty
'MS_Description', N'客户单号',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'ERPNumber'
GO
EXEC sp_addextendedproperty
'MS_Description', N'商品编号',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'SkuCode'
GO
EXEC sp_addextendedproperty
'MS_Description', N'商品名称',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'SkuName'
GO
EXEC sp_addextendedproperty
'MS_Description', N'商品状态',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'SkuStatus'
GO
EXEC sp_addextendedproperty
'MS_Description', N'单位',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'SkuUnit'
GO
EXEC sp_addextendedproperty
'MS_Description', N'批次号',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'BatchNumber'
GO
EXEC sp_addextendedproperty
'MS_Description', N'有效日期',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'ProductionDate'
GO
EXEC sp_addextendedproperty
'MS_Description', N'生产日期',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'ValidityDate'
GO
EXEC sp_addextendedproperty
'MS_Description', N'库位',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'ShelvesLoca'
GO
EXEC sp_addextendedproperty
'MS_Description', N'级别',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'LocaLevel'
GO
EXEC sp_addextendedproperty
'MS_Description', N'数量',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'TotalQuantity'
GO
EXEC sp_addextendedproperty
'MS_Description', N'临时数量',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'LTotalQuantity'
GO
EXEC sp_addextendedproperty
'MS_Description', N'占用数量',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'ZTotalQuantity'
GO
EXEC sp_addextendedproperty
'MS_Description', N'剩余数量',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'STotalQuantity'
GO
EXEC sp_addextendedproperty
'MS_Description', N'原入库数量',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'YTotalQuantity'
GO
EXEC sp_addextendedproperty
'MS_Description', N'备注',
'SCHEMA', N'dbo',
'TABLE', N'WMS_StockTemporarily',
'COLUMN', N'Remarks'
GO
-- ----------------------------
-- Records of [WMS_StockTemporarily]
-- ----------------------------
SET IDENTITY_INSERT [dbo].[WMS_StockTemporarily] ON
GO
INSERT INTO [dbo].[WMS_StockTemporarily] ([StockTemporarily_ID], [Stock_Id], [Company_Guid], [Warehouse_Guid], [Stock_Date], [OrderNumber], [ERPNumber], [ShelvesNumber], [SkuCode], [SkuName], [SkuStatus], [SkuUnit], [BatchNumber], [ProductionDate], [ValidityDate], [ShelvesLoca], [LocaLevel], [TotalQuantity], [LTotalQuantity], [ZTotalQuantity], [STotalQuantity], [YTotalQuantity], [Remarks], [CreateID], [CreateDate], [customText1], [customText2], [customText3], [customText4], [customText5], [customText6], [customText7], [customText8], [customText9], [customText10], [OrderBy_Id]) VALUES (N'30013', N'4004', N'40F7A909-A3EC-4EFE-AE80-BB01CB4E7964', N'8CC42CD4-3885-4642-8CFB-FA004BEABD40', N'03 8 2018 3:03PM', N'IGR18030800014', N'a20180308b', N'SJ18030800002', N'8689110', N'测试商品', N'非限制', N'件', N'20180308', N'', N'', N'A1001', N'2', N'200', N'0', N'1', N'200', N'200', N'', N'liuml', N'2018-03-08 15:03:32.157', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'1')
GO
SET IDENTITY_INSERT [dbo].[WMS_StockTemporarily] OFF
GO
AND (a.BatchNumber = b.BatchNumber OR b.BatchNumber ='')
得出的结果不对,发货数量是112值,但在WMS_StockTemporarily 表字段ZTotalQuantity 只更新到了111AND a.BatchNumber = b.BatchNumber
改为
AND (a.BatchNumber = b.BatchNumber OR a.BatchNumber IS NULL)
-- ----------------------------
-- Table structure for WMS_Out_OrderListPicking
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[WMS_Out_OrderListPicking]') AND type IN ('U'))
DROP TABLE [dbo].[WMS_Out_OrderListPicking]
GO
CREATE TABLE [dbo].[WMS_Out_OrderListPicking] (
[ListPicking_Id] bigint IDENTITY(1,1) NOT NULL,
[OrderList_Id] int NOT NULL,
[OrderNumber] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[ERPNumber] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[SkuCode] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[SkuName] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[SkuStatus] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[SkuUnit] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[BatchNumber] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[ProductionDate] date NULL,
[ValidityDate] date NULL,
[TotalQuantity] int NULL,
[PickingQuantity] int DEFAULT ((0)) NULL,
[TotalPackage] int NULL,
[TotalVolume] float(53) NULL,
[TotalWeight] float(53) NULL,
[Stock_Id] bigint NULL,
[Remarks] nvarchar(200) COLLATE Chinese_PRC_CI_AS NULL,
[customText1] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText2] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText3] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText4] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText5] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText6] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText7] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText8] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText9] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[customText10] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[Create_Id] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[Create_Date] datetime NULL,
[Modify_Id] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[Modify_Date] datetime NULL
)
GO
ALTER TABLE [dbo].[WMS_Out_OrderListPicking] SET (LOCK_ESCALATION = TABLE)
GO
EXEC sp_addextendedproperty
'MS_Description', N'订单编号',
'SCHEMA', N'dbo',
'TABLE', N'WMS_Out_OrderListPicking',
'COLUMN', N'OrderNumber'
GO
EXEC sp_addextendedproperty
'MS_Description', N'客户单号',
'SCHEMA', N'dbo',
'TABLE', N'WMS_Out_OrderListPicking',
'COLUMN', N'ERPNumber'
GO
EXEC sp_addextendedproperty
'MS_Description', N'商品代码',
'SCHEMA', N'dbo',
'TABLE', N'WMS_Out_OrderListPicking',
'COLUMN', N'SkuCode'
GO
EXEC sp_addextendedproperty
'MS_Description', N'商品名称',
'SCHEMA', N'dbo',
'TABLE', N'WMS_Out_OrderListPicking',
'COLUMN', N'SkuName'
GO
EXEC sp_addextendedproperty
'MS_Description', N'商品状态',
'SCHEMA', N'dbo',
'TABLE', N'WMS_Out_OrderListPicking',
'COLUMN', N'SkuStatus'
GO
EXEC sp_addextendedproperty
'MS_Description', N'单位',
'SCHEMA', N'dbo',
'TABLE', N'WMS_Out_OrderListPicking',
'COLUMN', N'SkuUnit'
GO
EXEC sp_addextendedproperty
'MS_Description', N'批次号',
'SCHEMA', N'dbo',
'TABLE', N'WMS_Out_OrderListPicking',
'COLUMN', N'BatchNumber'
GO
EXEC sp_addextendedproperty
'MS_Description', N'生产日期',
'SCHEMA', N'dbo',
'TABLE', N'WMS_Out_OrderListPicking',
'COLUMN', N'ProductionDate'
GO
EXEC sp_addextendedproperty
'MS_Description', N'有效日期',
'SCHEMA', N'dbo',
'TABLE', N'WMS_Out_OrderListPicking',
'COLUMN', N'ValidityDate'
GO
EXEC sp_addextendedproperty
'MS_Description', N'数量',
'SCHEMA', N'dbo',
'TABLE', N'WMS_Out_OrderListPicking',
'COLUMN', N'TotalQuantity'
GO
EXEC sp_addextendedproperty
'MS_Description', N'包装数量',
'SCHEMA', N'dbo',
'TABLE', N'WMS_Out_OrderListPicking',
'COLUMN', N'TotalPackage'
GO
EXEC sp_addextendedproperty
'MS_Description', N'体积',
'SCHEMA', N'dbo',
'TABLE', N'WMS_Out_OrderListPicking',
'COLUMN', N'TotalVolume'
GO
EXEC sp_addextendedproperty
'MS_Description', N'重量',
'SCHEMA', N'dbo',
'TABLE', N'WMS_Out_OrderListPicking',
'COLUMN', N'TotalWeight'
GO
EXEC sp_addextendedproperty
'MS_Description', N'备注',
'SCHEMA', N'dbo',
'TABLE', N'WMS_Out_OrderListPicking',
'COLUMN', N'Remarks'
GO
-- ----------------------------
-- Records of [WMS_Out_OrderListPicking]
-- ----------------------------
SET IDENTITY_INSERT [dbo].[WMS_Out_OrderListPicking] ON
GO
INSERT INTO [dbo].[WMS_Out_OrderListPicking] ([ListPicking_Id], [OrderList_Id], [OrderNumber], [ERPNumber], [SkuCode], [SkuName], [SkuStatus], [SkuUnit], [BatchNumber], [ProductionDate], [ValidityDate], [TotalQuantity], [PickingQuantity], [TotalPackage], [TotalVolume], [TotalWeight], [Stock_Id], [Remarks], [customText1], [customText2], [customText3], [customText4], [customText5], [customText6], [customText7], [customText8], [customText9], [customText10], [Create_Id], [Create_Date], [Modify_Id], [Modify_Date]) VALUES (N'30016', N'2019', N'OGR18030800002', N'O201803038021', N'8689110', N'测试商品', N'非限制', N'件', N'', N'1900-01-01', N'1900-01-01', N'111', N'0', N'10', N'0.500000000000000', N'1.500000000000000', N'0', N'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'liuml', NULL, NULL, NULL)
GO
INSERT INTO [dbo].[WMS_Out_OrderListPicking] ([ListPicking_Id], [OrderList_Id], [OrderNumber], [ERPNumber], [SkuCode], [SkuName], [SkuStatus], [SkuUnit], [BatchNumber], [ProductionDate], [ValidityDate], [TotalQuantity], [PickingQuantity], [TotalPackage], [TotalVolume], [TotalWeight], [Stock_Id], [Remarks], [customText1], [customText2], [customText3], [customText4], [customText5], [customText6], [customText7], [customText8], [customText9], [customText10], [Create_Id], [Create_Date], [Modify_Id], [Modify_Date]) VALUES (N'30017', N'2020', N'OGR18030800002', N'O201803038021', N'8689110', N'测试商品', N'非限制', N'件', N'20180308', N'1900-01-01', N'1900-01-01', N'1', N'0', N'10', N'0.500000000000000', N'1.500000000000000', N'4004', N'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'liuml', NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[WMS_Out_OrderListPicking] OFF
GO