Use ATNDBTest
Go


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 dbo.Broker
	DROP CONSTRAINT FK_Broker_OfficeDetail
GO
ALTER TABLE dbo.OfficeDetail SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Broker
	DROP CONSTRAINT FK_Broker_Users
GO
ALTER TABLE dbo.Users SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Broker
	DROP CONSTRAINT FK_Broker_AccEntityL2CodeBook
GO
ALTER TABLE dbo.AccEntityL2CodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Broker
	DROP CONSTRAINT FK_Broker_AccEntityL1CodeBook
GO
ALTER TABLE dbo.AccEntityL1CodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Broker
	DROP CONSTRAINT FK_Broker_BrokerKind
GO
ALTER TABLE dbo.BrokerKind SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
If Exists(Select * From sys.Views Where name = 'FactorHeaderAndDetailAbstractList') Drop View dbo.FactorHeaderAndDetailAbstractList
GO
ALTER TABLE dbo.Broker
	DROP CONSTRAINT DF_Broker_Active
GO
CREATE TABLE dbo.Tmp_Broker
	(
	Serial bigint NOT NULL,
	Code bigint NOT NULL,
	Name varchar(50) NULL,
	BrokerKindRef smallint NULL,
	Active bit NULL,
	Manager varchar(50) NULL,
	Address varchar(150) NULL,
	PostCode varchar(20) NULL,
	Tel1 varchar(20) NULL,
	Tel2 varchar(20) NULL,
	Fax varchar(20) NULL,
	Mobile varchar(20) NULL,
	SSN varchar(50) NULL,
	BirthCertificateNo varchar(50) NULL,
	BrokerPercentTypeRef smallint NULL,
	AccEntityL1CodeBookRef bigint NULL,
	AccEntityL2CodeBookRef bigint NULL,
	SaleBrokerPercent decimal(6, 3) NULL,
	RecorderRef int NULL,
	RecordDate char(10) NULL,
	Description varchar(300) NULL,
	OfficeDetailRef int NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Broker SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_Broker ADD CONSTRAINT
	DF_Broker_Active DEFAULT ((0)) FOR Active
GO
IF EXISTS(SELECT * FROM dbo.Broker)
	 EXEC('INSERT INTO dbo.Tmp_Broker (Serial, Code, Name, BrokerKindRef, Active, Manager, Address, PostCode, Tel1, Tel2, Fax, Mobile, SSN, AccEntityL1CodeBookRef, AccEntityL2CodeBookRef, SaleBrokerPercent, RecorderRef, RecordDate, Description, OfficeDetailRef)
		SELECT Serial, Code, Name, BrokerKindRef, Active, Manager, Address, PostCode, Tel1, Tel2, Fax, Mobile, SSN, AccEntityL1CodeBookRef, AccEntityL2CodeBookRef, SaleBrokerPercent, RecorderRef, RecordDate, Description, OfficeDetailRef FROM dbo.Broker WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.FactorHeader
	DROP CONSTRAINT FK_FactorHeader_Broker
GO
DROP TABLE dbo.Broker
GO
EXECUTE sp_rename N'dbo.Tmp_Broker', N'Broker', 'OBJECT' 
GO
ALTER TABLE dbo.Broker ADD CONSTRAINT
	PK_Broker PRIMARY KEY CLUSTERED 
	(
	Serial
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Broker ADD CONSTRAINT
	FK_Broker_BrokerKind FOREIGN KEY
	(
	BrokerKindRef
	) REFERENCES dbo.BrokerKind
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Broker ADD CONSTRAINT
	FK_Broker_AccEntityL1CodeBook FOREIGN KEY
	(
	AccEntityL1CodeBookRef
	) REFERENCES dbo.AccEntityL1CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Broker ADD CONSTRAINT
	FK_Broker_AccEntityL2CodeBook FOREIGN KEY
	(
	AccEntityL2CodeBookRef
	) REFERENCES dbo.AccEntityL2CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Broker ADD CONSTRAINT
	FK_Broker_Users FOREIGN KEY
	(
	RecorderRef
	) REFERENCES dbo.Users
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Broker ADD CONSTRAINT
	FK_Broker_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.FactorHeader ADD CONSTRAINT
	FK_FactorHeader_Broker FOREIGN KEY
	(
	BrokerRef
	) REFERENCES dbo.Broker
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.FactorHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

/*=============================================================================================================*/
Create Table ATNDBTest.dbo.BrokerPercentType(
	Serial smallint Not Null,
	[FA_Des] Varchar(50) Null, 
	[EN_Des] Varchar(50) Null)
GO
Alter Table ATNDBTest.dbo.BrokerPercentType Add Constraint PK_BrokerPercentType Primary Key Clustered (Serial)
GO
/*=============================================================================================================*/
INSERT [ATNDBTest].[dbo].[BrokerPercentType] ([Serial], [FA_Des], [EN_Des]) VALUES (1, N'محاسبه پورسانت برحسب فروش', N'Percent by sales')
INSERT [ATNDBTest].[dbo].[BrokerPercentType] ([Serial], [FA_Des], [EN_Des]) VALUES (2, N'محاسبه پورسانت برحسب سـود', N'Percent by profit')
/*=============================================================================================================*/
Update [ATNDBTest].[dbo].[Broker] Set BrokerPercentTypeRef = 1

