GoldHoo/doc/调整记录/已更新正式环境/PurchaseOrder-2018.08.15.sql

/*
   2018年8月15日10:45:40
   用户: goldhooUser
   服务器: 39.106.194.30\SQL2012,11433
   数据库: befri_Goldhoo_Dev
   应用程序: 
*/

/* 为了防止任何可能出现的数据丢失问题，您应该先仔细检查此脚本，然后再在数据库设计器的上下文之外运行此脚本。*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE shop_order.PurchaseOrder
	DROP CONSTRAINT FK_PurchaseOrder_Shop
GO
ALTER TABLE basedata.Shop SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
select Has_Perms_By_Name(N'basedata.Shop', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'basedata.Shop', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'basedata.Shop', 'Object', 'CONTROL') as Contr_Per BEGIN TRANSACTION
GO
ALTER TABLE shop_order.PurchaseOrder
	DROP CONSTRAINT DF__PurchaseO__Sales__167EEC3C
GO
ALTER TABLE shop_order.PurchaseOrder
	DROP CONSTRAINT DF_PurchaseOrder_CreditType
GO
ALTER TABLE shop_order.PurchaseOrder
	DROP CONSTRAINT DF__PurchaseO__Prepa__17731075
GO
ALTER TABLE shop_order.PurchaseOrder
	DROP CONSTRAINT DF__PurchaseO__Appro__186734AE
GO
ALTER TABLE shop_order.PurchaseOrder
	DROP CONSTRAINT DF__PurchaseO__Close__195B58E7
GO
ALTER TABLE shop_order.PurchaseOrder
	DROP CONSTRAINT DF__PurchaseO__Versi__1A4F7D20
GO
ALTER TABLE shop_order.PurchaseOrder
	DROP CONSTRAINT DF__PurchaseO__Creat__1B43A159
GO
ALTER TABLE shop_order.PurchaseOrder
	DROP CONSTRAINT DF__PurchaseO__Modif__1C37C592
GO
ALTER TABLE shop_order.PurchaseOrder
	DROP CONSTRAINT DF_PurchaseOrder_ErpStatus
GO
CREATE TABLE shop_order.Tmp_PurchaseOrder
	(
	PurchaseOrderId int NOT NULL,
	ShopId int NOT NULL,
	OrderNumber varchar(20) NOT NULL,
	OrderType tinyint NOT NULL,
	SalesType tinyint NOT NULL,
	CreditType int NOT NULL,
	SalesActivityId int NULL,
	OrderedOn datetime NULL,
	RequiredOn date NULL,
	PreviousVoucherId int NULL,
	PromoActivityId int NULL,
	Amount money NULL,
	ShippingMode tinyint NOT NULL,
	ShipedOn date NULL,
	ReceiptedAt nvarchar(100) NULL,
	PreparedBy nvarchar(20) NULL,
	PreparedOn datetime NOT NULL,
	Approved bit NOT NULL,
	ApprovedBy nvarchar(20) NULL,
	ApprovedOn datetime NULL,
	ApprovedResult tinyint NULL,
	ApprovedIdea nvarchar(200) NULL,
	Closed bit NOT NULL,
	ClosedBy nvarchar(20) NULL,
	ClosedOn datetime NULL,
	ClosedReason nvarchar(200) NULL,
	OrderStatus 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,
	SubmitOn datetime NULL,
	SubmitBy nvarchar(20) NULL,
	ErpStatus tinyint NULL,
	BusinessUnitCode varchar(20) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE shop_order.Tmp_PurchaseOrder SET (LOCK_ESCALATION = TABLE)
GO
DECLARE @v sql_variant 
SET @v = N'采购订单'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', NULL, NULL
GO
DECLARE @v sql_variant 
SET @v = cast(N'采购订单内码' as varchar(12))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'PurchaseOrderId'
GO
DECLARE @v sql_variant 
SET @v = cast(N'专卖店内码' as varchar(10))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'ShopId'
GO
DECLARE @v sql_variant 
SET @v = cast(N'订单编号-- 系统自动产生具有唯一值的编码' as varchar(39))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'OrderNumber'
GO
DECLARE @v sql_variant 
SET @v = cast(N'订单类型' as varchar(8))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'OrderType'
GO
DECLARE @v sql_variant 
SET @v = cast(N'销售类型' as varchar(8))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'SalesType'
GO
DECLARE @v sql_variant 
SET @v = N'扣款类型'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'CreditType'
GO
DECLARE @v sql_variant 
SET @v = cast(N'促销活动内码' as varchar(12))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'SalesActivityId'
GO
DECLARE @v sql_variant 
SET @v = cast(N'订货日期' as varchar(8))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'OrderedOn'
GO
DECLARE @v sql_variant 
SET @v = cast(N'需求日期' as varchar(8))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'RequiredOn'
GO
DECLARE @v sql_variant 
SET @v = cast(N'前置单据内码' as varchar(12))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'PreviousVoucherId'
GO
DECLARE @v sql_variant 
SET @v = N'促销活动内码'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'PromoActivityId'
GO
DECLARE @v sql_variant 
SET @v = cast(N'总金额' as varchar(6))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'Amount'
GO
DECLARE @v sql_variant 
SET @v = cast(N'发运方式' as varchar(8))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'ShippingMode'
GO
DECLARE @v sql_variant 
SET @v = cast(N'发货日期' as varchar(8))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'ShipedOn'
GO
DECLARE @v sql_variant 
SET @v = cast(N'收货地址' as varchar(8))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'ReceiptedAt'
GO
DECLARE @v sql_variant 
SET @v = cast(N'制单人' as varchar(6))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'PreparedBy'
GO
DECLARE @v sql_variant 
SET @v = cast(N'制单日期--系统自动填充当前日期' as varchar(30))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'PreparedOn'
GO
DECLARE @v sql_variant 
SET @v = cast(N'审核标记' as varchar(8))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'Approved'
GO
DECLARE @v sql_variant 
SET @v = cast(N'审核人内码--执行审核操作时，系统自动填充当前登录用户内码' as varchar(56))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'ApprovedBy'
GO
DECLARE @v sql_variant 
SET @v = cast(N'审核日期--执行审核操作时，系统自动填充当前日期' as varchar(46))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'ApprovedOn'
GO
DECLARE @v sql_variant 
SET @v = cast(N'审核结果-- -1未审核 ,1 通过, 0 否决' as varchar(35))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'ApprovedResult'
GO
DECLARE @v sql_variant 
SET @v = cast(N'审核意见' as varchar(8))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'ApprovedIdea'
GO
DECLARE @v sql_variant 
SET @v = cast(N'是否关闭--当子表是所有项目都关闭时,此采购订单关闭.1为关闭 ' as varchar(58))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'Closed'
GO
DECLARE @v sql_variant 
SET @v = cast(N'关闭人内码' as varchar(10))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'ClosedBy'
GO
DECLARE @v sql_variant 
SET @v = cast(N'关闭日期' as varchar(8))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'ClosedOn'
GO
DECLARE @v sql_variant 
SET @v = cast(N'关闭原因' as varchar(8))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'ClosedReason'
GO
DECLARE @v sql_variant 
SET @v = cast(N'订单状态' as varchar(8))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'OrderStatus'
GO
DECLARE @v sql_variant 
SET @v = cast(N'记录版本' as varchar(8))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'VersionNumber'
GO
DECLARE @v sql_variant 
SET @v = cast(N'记录创建人' as varchar(10))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'CreatedBy'
GO
DECLARE @v sql_variant 
SET @v = cast(N'记录创建时间' as varchar(12))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'CreatedOn'
GO
DECLARE @v sql_variant 
SET @v = cast(N'记录修改人' as varchar(10))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'ModifiedBy'
GO
DECLARE @v sql_variant 
SET @v = cast(N'记录修改时间' as varchar(12))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'ModifiedOn'
GO
DECLARE @v sql_variant 
SET @v = cast(N'备注' as varchar(4))
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'Description'
GO
DECLARE @v sql_variant 
SET @v = N'提交时间'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'SubmitOn'
GO
DECLARE @v sql_variant 
SET @v = N'提交人'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'SubmitBy'
GO
DECLARE @v sql_variant 
SET @v = N'Erp状态'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'shop_order', N'TABLE', N'Tmp_PurchaseOrder', N'COLUMN', N'ErpStatus'
GO
ALTER TABLE shop_order.Tmp_PurchaseOrder ADD CONSTRAINT
	DF__PurchaseO__Sales__167EEC3C DEFAULT ((1)) FOR SalesType
GO
ALTER TABLE shop_order.Tmp_PurchaseOrder ADD CONSTRAINT
	DF_PurchaseOrder_CreditType DEFAULT ((1)) FOR CreditType
GO
ALTER TABLE shop_order.Tmp_PurchaseOrder ADD CONSTRAINT
	DF__PurchaseO__Prepa__17731075 DEFAULT (getdate()) FOR PreparedOn
GO
ALTER TABLE shop_order.Tmp_PurchaseOrder ADD CONSTRAINT
	DF__PurchaseO__Appro__186734AE DEFAULT ((0)) FOR Approved
GO
ALTER TABLE shop_order.Tmp_PurchaseOrder ADD CONSTRAINT
	DF__PurchaseO__Close__195B58E7 DEFAULT ((0)) FOR Closed
GO
ALTER TABLE shop_order.Tmp_PurchaseOrder ADD CONSTRAINT
	DF__PurchaseO__Versi__1A4F7D20 DEFAULT ((1)) FOR VersionNumber
GO
ALTER TABLE shop_order.Tmp_PurchaseOrder ADD CONSTRAINT
	DF__PurchaseO__Creat__1B43A159 DEFAULT (getdate()) FOR CreatedOn
GO
ALTER TABLE shop_order.Tmp_PurchaseOrder ADD CONSTRAINT
	DF__PurchaseO__Modif__1C37C592 DEFAULT (getdate()) FOR ModifiedOn
GO
ALTER TABLE shop_order.Tmp_PurchaseOrder ADD CONSTRAINT
	DF_PurchaseOrder_ErpStatus DEFAULT ((0)) FOR ErpStatus
GO
IF EXISTS(SELECT * FROM shop_order.PurchaseOrder)
	 EXEC('INSERT INTO shop_order.Tmp_PurchaseOrder (PurchaseOrderId, ShopId, OrderNumber, OrderType, SalesType, CreditType, SalesActivityId, OrderedOn, RequiredOn, PreviousVoucherId, Amount, ShippingMode, ShipedOn, ReceiptedAt, PreparedBy, PreparedOn, Approved, ApprovedBy, ApprovedOn, ApprovedResult, ApprovedIdea, Closed, ClosedBy, ClosedOn, ClosedReason, OrderStatus, VersionNumber, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn, Description, SubmitOn, SubmitBy, ErpStatus, BusinessUnitCode)
		SELECT PurchaseOrderId, ShopId, OrderNumber, OrderType, SalesType, CreditType, SalesActivityId, OrderedOn, RequiredOn, PreviousVoucherId, Amount, ShippingMode, ShipedOn, ReceiptedAt, PreparedBy, PreparedOn, Approved, ApprovedBy, ApprovedOn, ApprovedResult, ApprovedIdea, Closed, ClosedBy, ClosedOn, ClosedReason, OrderStatus, VersionNumber, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn, Description, SubmitOn, SubmitBy, ErpStatus, BusinessUnitCode FROM shop_order.PurchaseOrder WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE shop_order.PurchaseOrderLine
	DROP CONSTRAINT FK_PurchaseOrderLine_PurchaseOrder
GO
DROP TABLE shop_order.PurchaseOrder
GO
EXECUTE sp_rename N'shop_order.Tmp_PurchaseOrder', N'PurchaseOrder', 'OBJECT' 
GO
ALTER TABLE shop_order.PurchaseOrder ADD CONSTRAINT
	PK_PURCHASEORDER PRIMARY KEY CLUSTERED 
	(
	PurchaseOrderId
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE shop_order.PurchaseOrder ADD CONSTRAINT
	FK_PurchaseOrder_Shop FOREIGN KEY
	(
	ShopId
	) REFERENCES basedata.Shop
	(
	ShopId
	) ON UPDATE  CASCADE 
	 ON DELETE  CASCADE 
	
GO
COMMIT
select Has_Perms_By_Name(N'shop_order.PurchaseOrder', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'shop_order.PurchaseOrder', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'shop_order.PurchaseOrder', 'Object', 'CONTROL') as Contr_Per BEGIN TRANSACTION
GO
ALTER TABLE shop_order.PurchaseOrderLine ADD CONSTRAINT
	FK_PurchaseOrderLine_PurchaseOrder FOREIGN KEY
	(
	PurchaseOrderId
	) REFERENCES shop_order.PurchaseOrder
	(
	PurchaseOrderId
	) ON UPDATE  CASCADE 
	 ON DELETE  CASCADE 
	
GO
ALTER TABLE shop_order.PurchaseOrderLine SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
select Has_Perms_By_Name(N'shop_order.PurchaseOrderLine', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'shop_order.PurchaseOrderLine', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'shop_order.PurchaseOrderLine', 'Object', 'CONTROL') as Contr_Per