【高手来】合并数量
1. 当HampooPN,BatchId,WarehouseId,ShelfId相同时，其它栏位的信息可能不同。
2. 当HampooPN,BatchId,WarehouseId,ShelfId相同时，合并Quantity
3. 当HampooPN,BatchId,WarehouseId,ShelfId相同时，只需要返回任意一条记录，并且Quantity是合并后的数量。
USE [hamp]
GO
/****** 对象: Table [dbo].[InventoryAllocationDocSub] 脚本日期: 07/06/2013 15:32:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[InventoryAllocationDocSub](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DocId] [int] NOT NULL,
[ProjectId] [int] NULL,
[DetailIdOut] [int] NULL,
[DetailIdIn] [int] NULL,
[MaterialId] [int] NULL,
[MaterialName] [varchar](128) COLLATE Chinese_PRC_CI_AS NULL,
[HampooPN] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[SupplierPN] [varchar](384) COLLATE Chinese_PRC_CI_AS NULL,
[ClientPN] [varchar](384) COLLATE Chinese_PRC_CI_AS NULL,
[WarehouseId] [int] NULL,
[ShelfId] [int] NULL,
[WarehouseIdBack] [int] NULL,
[ShelfIdBack] [int] NULL,
[BatchId] [int] NULL,
[PackageId] [int] NULL,
[Standard] [varchar](256) COLLATE Chinese_PRC_CI_AS NULL,
[Unit] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Quantity] [decimal](19, 6) NULL,
[SubUnit] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[SubQuantity] [decimal](19, 6) NULL,
[UnitRate] [decimal](19, 6) NULL,
[SourceDoc] [varchar](1000) COLLATE Chinese_PRC_CI_AS NULL,
[SaleOrderNo] [varchar](2000) COLLATE Chinese_PRC_CI_AS NULL,
[ProduceOrderNo] [varchar](2000) COLLATE Chinese_PRC_CI_AS NULL,
[PurchaseOrderNo] [varchar](2000) COLLATE Chinese_PRC_CI_AS NULL,
[MaterialPlanOrderNo] [varchar](2000) COLLATE Chinese_PRC_CI_AS NULL,
[ProducePlanOrderNo] [varchar](2000) COLLATE Chinese_PRC_CI_AS NULL,
[PurchasePlanOrderNo] [varchar](2000) COLLATE Chinese_PRC_CI_AS NULL,
[TaxPrice] [decimal](19, 6) NULL,
[OrderCount] [decimal](19, 6) NULL,
[SupplyType] [int] NULL,
[OrderAddupCount] [decimal](19, 6) NULL,
[AddupCount] [decimal](19, 6) NULL,
[Status] [int] NOT NULL CONSTRAINT [DF__Inventory__Statu__65989063] DEFAULT ((0)),
[ProductionDate] [datetime] NULL,
[ExpirationDate] [datetime] NULL,
[ProductionDayCode] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Remark] [nvarchar](2000) COLLATE Chinese_PRC_CI_AS NULL,
[AddupCount1] [decimal](19, 6) NULL,
[Status1] [int] NULL,
[ExDeci1] [decimal](19, 6) NULL,
[ExDeci2] [decimal](19, 6) NULL,
CONSTRAINT [PK_INVENTORYALLOCATIONDOCSUB] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单据副表标识' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单据表标识' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'DocId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'项目编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'ProjectId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'库存明细表编号(出库)' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'DetailIdOut'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'库存明细表编号(入库)' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'DetailIdIn'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'物料表标识' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'MaterialId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'物料名称' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'MaterialName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'物料编码' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'HampooPN'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'供应商料号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'SupplierPN'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户料号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'ClientPN'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仓库编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'WarehouseId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'货架编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'ShelfId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'批次编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'BatchId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'分装编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'PackageId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'规格' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'Standard'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单位' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'Unit'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数量' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'Quantity'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'辅单位' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'SubUnit'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'辅数量' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'SubQuantity'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单位换算率' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'UnitRate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'源单据副表编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'SourceDoc'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'销售订单号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'SaleOrderNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生产订单号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'ProduceOrderNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'采购订单号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'PurchaseOrderNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生产计划单号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'ProducePlanOrderNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'物料来源类型(1，汉普采购；2，客供；3，汉普采购转客供)' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'SupplyType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'副表状态' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'Status'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生产日期' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'ProductionDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'过期日期' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'ExpirationDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub', @level2type=N'COLUMN', @level2name=N'Remark'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'调拨单据副表' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'InventoryAllocationDocSub'
