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