GoldHoo/doc/调整记录/已更新正式环境/2016-09-26(结构).sql

USE [befri_Goldhoo]
GO
/****** Object:  UserDefinedFunction [basedata].[f_GetInventoryCategory]    Script Date: 2016-09-26 09:55:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 开发人员:  
-- 开发日期： 
-- 功    能: 递归获取指定产品分类的全部下级产品分类。

-- <参数定义>
-- <param name="InventoryCategoryId">需要返回数据的客户区域内码</param>
-- </参数定义>

-- 测    试: select * from [basedata].[f_GetInventoryCategory](78)
-- =============================================
CREATE FUNCTION [basedata].[f_GetInventoryCategory]
    (
      @InventoryCategoryId INT = NULL      
    )
RETURNS @result TABLE
    (
      InventoryCategoryId INT ,
      CategoryName NVARCHAR(100) ,
      ParentId INT
    )
AS
    BEGIN
        WITH    CTE ( InventoryCategoryId, CategoryName, ParentId )
                  AS ( SELECT   InventoryCategoryId ,
                                CategoryName ,
                                ParentId
                       FROM     basedata.InventoryCategory
                       WHERE    InventoryCategoryId = @InventoryCategoryId
                       UNION ALL
                       SELECT   sub.InventoryCategoryId ,
                                sub.CategoryName ,
                                sub.ParentId
                       FROM     basedata.InventoryCategory sub
                                JOIN CTE cte ON sub.ParentId = cte.InventoryCategoryId
                     )
            INSERT  INTO @result
                    SELECT  InventoryCategoryId ,
                            CategoryName ,
                            ParentId
                    FROM    CTE
            OPTION  ( MAXRECURSION 0 )

        RETURN 
    END

CREATE VIEW clearance.V_Inventory
AS
    SELECT  i.InventoryId ,
            i.InventoryNumber ,
            i.InventoryName ,
            i.InventoryCategoryId ,
            i.Spec ,
            i.AliasName ,
            i.SearchCode ,
            i.SalesPrice ,
            i.IsClearance ,
            ISNULL(ca.DiscountCoef, 0) DiscountCoef ,
            ISNULL(ca.Quantity, 0) Quantity ,
            i.Description
    FROM    basedata.Inventory i
            LEFT JOIN clearance.ClearanceAcc ca ON ca.InventoryId = i.InventoryId
    WHERE   InventoryTypeId IN ( 1, 3 ) 
	 


CREATE VIEW clearance.V_ClearanceAccDetail
AS
    SELECT  cad.ClearanceAccDetailId ,
            cad.TableName ,
            cad.VoucherLineId ,
            cad.OperationType ,
            cad.WarehouseId ,
            cad.InventoryId ,
            i.InventoryNumber ,
            i.InventoryName ,
            cad.IncreasedQty ,
            cad.DecreasedQty ,
            cad.DiscountCoef ,
            cad.AccountedOn ,
            cad.Description
    FROM    clearance.ClearanceAccDetail AS cad
            INNER JOIN basedata.Inventory AS i ON i.InventoryId = cad.InventoryId


USE [befri_Goldhoo]
GO
/****** Object:  Table [clearance].[ClearanceAcc]    Script Date: 2016-09-27 10:18:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [clearance].[ClearanceAcc]
    (
      [WarehouseId] [int] NOT NULL ,
      [InventoryId] [int] NOT NULL ,
      [Quantity] [numeric](9, 0) NOT NULL ,
      [DiscountCoef] [numeric](5, 2) NULL ,
      CONSTRAINT [PK_CLEARANCEACC] PRIMARY KEY CLUSTERED
        ( [WarehouseId] ASC, [InventoryId] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'仓库内码',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceAcc',
    @level2type = N'COLUMN', @level2name = N'WarehouseId'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'存货内码',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceAcc',
    @level2type = N'COLUMN', @level2name = N'InventoryId'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'数量',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceAcc',
    @level2type = N'COLUMN', @level2name = N'Quantity'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'折扣系数',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceAcc',
    @level2type = N'COLUMN', @level2name = N'DiscountCoef'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'清仓产品账务',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceAcc'
GO


USE [befri_Goldhoo]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [clearance].[ClearanceAccDetail]
    (
      [ClearanceAccDetailId] [int] IDENTITY(1, 1)
                                   NOT NULL ,
      [TableName] [varchar](100) NOT NULL ,
      [VoucherLineId] [int] NOT NULL ,
      [OperationType] [tinyint] NOT NULL ,
      [WarehouseId] [int] NOT NULL ,
      [InventoryId] [int] NOT NULL ,
      [IncreasedQty] [numeric](9, 0) NOT NULL ,
      [DecreasedQty] [numeric](9, 0) NOT NULL ,
      [DiscountCoef] [numeric](5, 2) NULL ,
      [AccountedOn] [datetime] NOT NULL ,
      [Description] [nvarchar](200) NULL ,
      CONSTRAINT [PK_CLEARANCEACCDETAIL] PRIMARY KEY CLUSTERED
        ( [ClearanceAccDetailId] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [clearance].[ClearanceAccDetail] ADD  CONSTRAINT [DF__Clearance__Incre__5837A100]  DEFAULT ((0.00)) FOR [IncreasedQty]
GO
ALTER TABLE [clearance].[ClearanceAccDetail] ADD  CONSTRAINT [DF__Clearance__Decre__592BC539]  DEFAULT ((0.00)) FOR [DecreasedQty]
GO
ALTER TABLE [clearance].[ClearanceAccDetail] ADD  CONSTRAINT [DF__Clearance__Accou__5A1FE972]  DEFAULT (GETDATE()) FOR [AccountedOn]
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'单据行数据表',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceAccDetail',
    @level2type = N'COLUMN', @level2name = N'TableName'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'单据行内码',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceAccDetail',
    @level2type = N'COLUMN', @level2name = N'VoucherLineId'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'单据操作类型',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceAccDetail',
    @level2type = N'COLUMN', @level2name = N'OperationType'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'仓库内码',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceAccDetail',
    @level2type = N'COLUMN', @level2name = N'WarehouseId'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'存货内码',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceAccDetail',
    @level2type = N'COLUMN', @level2name = N'InventoryId'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'增加数量',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceAccDetail',
    @level2type = N'COLUMN', @level2name = N'IncreasedQty'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'减少数量',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceAccDetail',
    @level2type = N'COLUMN', @level2name = N'DecreasedQty'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'折扣系数',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceAccDetail',
    @level2type = N'COLUMN', @level2name = N'DiscountCoef'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'记账日期',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceAccDetail',
    @level2type = N'COLUMN', @level2name = N'AccountedOn'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'备注',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceAccDetail',
    @level2type = N'COLUMN', @level2name = N'Description'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description',
    @value = N'清仓管理.清仓产品账务明细', @level0type = N'SCHEMA',
    @level0name = N'clearance', @level1type = N'TABLE',
    @level1name = N'ClearanceAccDetail'
GO

USE [befri_Goldhoo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [clearance].[ClearanceRec]
    (
      [ClearanceRecId] [int] NOT NULL ,
      [RecType] [tinyint] NOT NULL ,
      [WarehouseId] [int] NOT NULL ,
      [VoucherNumber] [varchar](20) NOT NULL ,
      [VoucherDate] [datetime] NOT NULL ,
      [Summary] [nvarchar](200) NULL ,
      [BusinessDate] [datetime] NULL ,
      [PreparedBy] [nvarchar](20) NOT NULL ,
      [PreparedOn] [datetime] NOT NULL ,
      [Approved] [bit] NOT NULL ,
      [ApprovedBy] [nvarchar](20) NULL ,
      [ApprovedOn] [datetime] NULL ,
      [ApprovedResult] [tinyint] NULL ,
      [CancelApproved] [bit] NOT NULL ,
      [CancelApprovedBy] [nvarchar](20) NULL ,
      [CancelApprovedOn] [datetime] NULL ,
      [CancelApprovedReson] [nvarchar](100) NULL ,
      [HandledBy] [nvarchar](20) NULL ,
      [Status] [tinyint] NOT NULL ,
      [VersionNumber] [smallint] NOT NULL ,
      [CreatedBy] [nvarchar](20) NOT NULL ,
      [CreatedOn] [datetime] NOT NULL ,
      [ModifiedBy] [nvarchar](20) NULL ,
      [ModifiedOn] [datetime] NOT NULL ,
      [Description] [nvarchar](200) NULL ,
      CONSTRAINT [PK_CLEARANCEREC] PRIMARY KEY CLUSTERED
        ( [ClearanceRecId] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [clearance].[ClearanceRec] ADD  CONSTRAINT [DF__Clearance__Appro__5CFC561D]  DEFAULT ((0)) FOR [Approved]
GO
ALTER TABLE [clearance].[ClearanceRec] ADD  CONSTRAINT [DF__Clearance__Cance__5DF07A56]  DEFAULT ((0)) FOR [CancelApproved]
GO
ALTER TABLE [clearance].[ClearanceRec] ADD  CONSTRAINT [DF__Clearance__Versi__5EE49E8F]  DEFAULT ((0)) FOR [VersionNumber]
GO
ALTER TABLE [clearance].[ClearanceRec] ADD  CONSTRAINT [DF__Clearance__Creat__5FD8C2C8]  DEFAULT (GETDATE()) FOR [CreatedOn]
GO
ALTER TABLE [clearance].[ClearanceRec] ADD  CONSTRAINT [DF__Clearance__Modif__60CCE701]  DEFAULT (GETDATE()) FOR [ModifiedOn]
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description',
    @value = N'存货入出库记录内码', @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'ClearanceRecId'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'仓库内码',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'WarehouseId'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'单据号',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'VoucherNumber'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'单据日期',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'VoucherDate'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'单据摘要',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'Summary'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'入出库日期',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'BusinessDate'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'制单人内码',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'PreparedBy'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description',
    @value = N'制单日期--系统自动填充当前日期', @level0type = N'SCHEMA',
    @level0name = N'clearance', @level1type = N'TABLE',
    @level1name = N'ClearanceRec', @level2type = N'COLUMN',
    @level2name = N'PreparedOn'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'审核标记',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'Approved'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description',
    @value = N'审核人内码--执行审核操作时,系统自动填充当前登录用户内码', @level0type = N'SCHEMA',
    @level0name = N'clearance', @level1type = N'TABLE',
    @level1name = N'ClearanceRec', @level2type = N'COLUMN',
    @level2name = N'ApprovedBy'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description',
    @value = N'审核日期--执行审核操作时，系统自动填充当前日期', @level0type = N'SCHEMA',
    @level0name = N'clearance', @level1type = N'TABLE',
    @level1name = N'ClearanceRec', @level2type = N'COLUMN',
    @level2name = N'ApprovedOn'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description',
    @value = N'审核结果-- 0：未审核 ；1： 通过,；2： 否决', @level0type = N'SCHEMA',
    @level0name = N'clearance', @level1type = N'TABLE',
    @level1name = N'ClearanceRec', @level2type = N'COLUMN',
    @level2name = N'ApprovedResult'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'弃审标记',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'CancelApproved'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'弃审人内码',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'CancelApprovedBy'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'弃审日期',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'CancelApprovedOn'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'弃审原因',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'CancelApprovedReson'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'经手人',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'HandledBy'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'单据状态',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'Status'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'记录版本',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'VersionNumber'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'记录创建人',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'CreatedBy'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'记录创建时间',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'CreatedOn'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'记录修改人',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'ModifiedBy'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'记录修改时间',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'ModifiedOn'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'备注',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRec',
    @level2type = N'COLUMN', @level2name = N'Description'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description',
    @value = N'清仓管理.清仓产品入出库记录', @level0type = N'SCHEMA',
    @level0name = N'clearance', @level1type = N'TABLE',
    @level1name = N'ClearanceRec'
GO

USE [befri_Goldhoo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [clearance].[ClearanceRecLine]
    (
      [ClearanceRecLineId] [int] NOT NULL ,
      [ClearanceRecId] [int] NOT NULL ,
      [InventoryId] [int] NOT NULL ,
      [InventoryNumber] [varchar](20) NULL ,
      [InventoryName] [nvarchar](50) NULL ,
      [Spec] [varchar](200) NULL ,
      [BatchNumber] [varchar](20) NULL ,
      [IncreasedQty] [numeric](9, 0) NOT NULL ,
      [DecreasedQty] [numeric](9, 0) NOT NULL ,
      [DiscountCoef] [numeric](5, 2) NULL ,
      [IncreasedPrice] [money] NULL ,
      [DecreasedPrice] [money] NULL ,
      [IncreasedMoney] [money] NULL ,
      [DecreasedMoney] [money] NULL ,
      [LineStatus] [tinyint] NULL ,
      [VersionNumber] [smallint] NOT NULL ,
      [Description] [nvarchar](200) NULL ,
      CONSTRAINT [PK_CLEARANCERECLINE] PRIMARY KEY CLUSTERED
        ( [ClearanceRecLineId] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [clearance].[ClearanceRecLine] ADD  CONSTRAINT [DF__Clearance__Versi__63A953AC]  DEFAULT ((0)) FOR [VersionNumber]
GO
ALTER TABLE [clearance].[ClearanceRecLine]  WITH CHECK ADD  CONSTRAINT [FK_ClearanceRecLine_ClearanceRec] FOREIGN KEY([ClearanceRecId])
REFERENCES [clearance].[ClearanceRec] ([ClearanceRecId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [clearance].[ClearanceRecLine] CHECK CONSTRAINT [FK_ClearanceRecLine_ClearanceRec]
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description',
    @value = N'存货入出库记录行内码', @level0type = N'SCHEMA',
    @level0name = N'clearance', @level1type = N'TABLE',
    @level1name = N'ClearanceRecLine', @level2type = N'COLUMN',
    @level2name = N'ClearanceRecLineId'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description',
    @value = N'存货入出库记录内码', @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRecLine',
    @level2type = N'COLUMN', @level2name = N'ClearanceRecId'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'存货内码',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRecLine',
    @level2type = N'COLUMN', @level2name = N'InventoryId'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'存货编码',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRecLine',
    @level2type = N'COLUMN', @level2name = N'InventoryNumber'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'存货名称',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRecLine',
    @level2type = N'COLUMN', @level2name = N'InventoryName'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'规格型号',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRecLine',
    @level2type = N'COLUMN', @level2name = N'Spec'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'批号',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRecLine',
    @level2type = N'COLUMN', @level2name = N'BatchNumber'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'入库数量',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRecLine',
    @level2type = N'COLUMN', @level2name = N'IncreasedQty'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'出库数量',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRecLine',
    @level2type = N'COLUMN', @level2name = N'DecreasedQty'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'折扣系数',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRecLine',
    @level2type = N'COLUMN', @level2name = N'DiscountCoef'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'入库单价',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRecLine',
    @level2type = N'COLUMN', @level2name = N'IncreasedPrice'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'出库单价',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRecLine',
    @level2type = N'COLUMN', @level2name = N'DecreasedPrice'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'增加金额',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRecLine',
    @level2type = N'COLUMN', @level2name = N'IncreasedMoney'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'减少金额',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRecLine',
    @level2type = N'COLUMN', @level2name = N'DecreasedMoney'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'行状态',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRecLine',
    @level2type = N'COLUMN', @level2name = N'LineStatus'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'记录版本',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRecLine',
    @level2type = N'COLUMN', @level2name = N'VersionNumber'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'备注',
    @level0type = N'SCHEMA', @level0name = N'clearance',
    @level1type = N'TABLE', @level1name = N'ClearanceRecLine',
    @level2type = N'COLUMN', @level2name = N'Description'
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description',
    @value = N'清仓管理.清仓产品入出库记录行', @level0type = N'SCHEMA',
    @level0name = N'clearance', @level1type = N'TABLE',
    @level1name = N'ClearanceRecLine'
GO


USE [befri_Goldhoo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 开发人员: lkaih
-- 开发日期: 2016-09
-- 功能描述: 当插入记录时自动更新清仓总帐
-- =============================================
ALTER TRIGGER [clearance].[tri_ClearanceAccDetail_INSERT] ON [clearance].[ClearanceAccDetail]
    FOR INSERT
AS
    BEGIN
        SET NOCOUNT ON;

        MERGE clearance.ClearanceAcc AS t
            USING INSERTED AS s
            ON ( s.WarehouseId = t.WarehouseId
                 AND s.InventoryId = t.InventoryId
               )
            WHEN MATCHED
                THEN			
				UPDATE
                    SET Quantity = Quantity + s.IncreasedQty - s.DecreasedQty ,
                        [DiscountCoef] = CASE WHEN s.IncreasedQty
                                                   - s.DecreasedQty > 0
                                              THEN s.[DiscountCoef]
                                              ELSE t.[DiscountCoef]
                                         END
            WHEN NOT MATCHED
                THEN
				INSERT  (
                          WarehouseId ,
                          InventoryId ,
                          Quantity ,
                          [DiscountCoef]
                        )
                    VALUES
                        ( s.WarehouseId ,
                          s.InventoryId ,
                          s.IncreasedQty - s.DecreasedQty ,
                          s.DiscountCoef
                        );
				 
    END