-- توجه: اگر فایل 32 اجرا شده است، دیگر نیازی به اجرای این فایل نیست
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.GetterOrPayerFactor
	DROP CONSTRAINT FK_GetterOrPayerFactor_Recorder
GO
ALTER TABLE dbo.GetterOrPayerFactor
	DROP CONSTRAINT FK_GetterOrPayerFactor_Editor
GO
ALTER TABLE dbo.Users SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GetterOrPayerFactor
	DROP CONSTRAINT FK_GetterOrPayerFactor_AccEntityL2CodeBook
GO
ALTER TABLE dbo.AccEntityL2CodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GetterOrPayerFactor
	DROP CONSTRAINT FK_GetterOrPayerFactor_AccEntityL1CodeBook
GO
ALTER TABLE dbo.AccEntityL1CodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GetterOrPayerFactor
	DROP CONSTRAINT FK_GetterOrPayerFactor_Degree
GO
ALTER TABLE dbo.Degree SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GetterOrPayerFactor
	DROP CONSTRAINT FK_GetterOrPayerFactor_GetterOrPayerFactorKind
GO
ALTER TABLE dbo.GetterOrPayerFactorKind SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GetterOrPayerFactor
	DROP CONSTRAINT DF_GetterOrPayerFactor_Active
GO
CREATE TABLE dbo.Tmp_GetterOrPayerFactor
	(
	Serial bigint NOT NULL,
	Code bigint NOT NULL,
	FAName varchar(50) NULL,
	ENName varchar(50) NULL,
	KindRef smallint NOT NULL,
	Active bit NULL,
	Manager varchar(50) NULL,
	DegreeRef smallint NULL,
	Address varchar(300) NULL,
	PostCode varchar(20) NULL,
	Email varchar(50) NULL,
	Website varchar(50) NULL,
	RegNo varchar(50) NULL,
	SSN varchar(50) NULL,
	EconomicCode varchar(50) NULL,
	Tel1 varchar(20) NULL,
	Tel2 varchar(20) NULL,
	Fax varchar(20) NULL,
	Mobile varchar(20) NULL,
	BankName varchar(50) NULL,
	OffshootCode varchar(15) NULL,
	OffshootName varchar(50) NULL,
	BankAccountNo varchar(20) NULL,
	IBAN varchar(30) NULL,
	BankAddress varchar(300) NULL,
	AccEntityL1CodeBookRef bigint NULL,
	AccEntityL2CodeBookRef bigint NULL,
	AccountMaximum bigint NULL,
	SaleProfitPercent decimal(6, 3) NULL,
	SaleRebatePercent decimal(6, 3) NULL,
	RecorderRef int NULL,
	RecordDate char(10) NULL,
	EditorRef int NULL,
	EditDate char(10) NULL,
	Description varchar(300) NULL,
	OfficeDetailRef int NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_GetterOrPayerFactor SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_GetterOrPayerFactor ADD CONSTRAINT
	DF_GetterOrPayerFactor_Active DEFAULT ((0)) FOR Active
GO
IF EXISTS(SELECT * FROM dbo.GetterOrPayerFactor)
	 EXEC('INSERT INTO dbo.Tmp_GetterOrPayerFactor (Serial, Code, FAName, KindRef, Active, Manager, DegreeRef, Address, PostCode, Email, Website, RegNo, SSN, EconomicCode, Tel1, Tel2, Fax, Mobile, BankName, OffshootCode, OffshootName, BankAccountNo, IBAN, BankAddress, AccEntityL1CodeBookRef, AccEntityL2CodeBookRef, AccountMaximum, SaleProfitPercent, SaleRebatePercent, RecorderRef, RecordDate, EditorRef, EditDate, Description, OfficeDetailRef)
		SELECT Serial, Code, Name, KindRef, Active, Manager, DegreeRef, Address, PostCode, Email, Website, RegNo, SSN, EconomicCode, Tel1, Tel2, Fax, Mobile, BankName, OffshootCode, OffshootName, BankAccountNo, IBAN, BankAddress, AccEntityL1CodeBookRef, AccEntityL2CodeBookRef, AccountMaximum, SaleProfitPercent, SaleRebatePercent, RecorderRef, RecordDate, EditorRef, EditDate, Description, OfficeDetailRef FROM dbo.GetterOrPayerFactor WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_GetterOrPayerFactor
GO
ALTER TABLE dbo.FactorHeader
	DROP CONSTRAINT FK_FactorHeader_GetterOrPayerFactor
GO
DROP TABLE dbo.GetterOrPayerFactor
GO
EXECUTE sp_rename N'dbo.Tmp_GetterOrPayerFactor', N'GetterOrPayerFactor', 'OBJECT' 
GO
ALTER TABLE dbo.GetterOrPayerFactor ADD CONSTRAINT
	PK_GetterOrPayerFactor 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.GetterOrPayerFactor ADD CONSTRAINT
	FK_GetterOrPayerFactor_GetterOrPayerFactorKind FOREIGN KEY
	(
	KindRef
	) REFERENCES dbo.GetterOrPayerFactorKind
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GetterOrPayerFactor ADD CONSTRAINT
	FK_GetterOrPayerFactor_Degree FOREIGN KEY
	(
	DegreeRef
	) REFERENCES dbo.Degree
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GetterOrPayerFactor ADD CONSTRAINT
	FK_GetterOrPayerFactor_AccEntityL1CodeBook FOREIGN KEY
	(
	AccEntityL1CodeBookRef
	) REFERENCES dbo.AccEntityL1CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GetterOrPayerFactor ADD CONSTRAINT
	FK_GetterOrPayerFactor_AccEntityL2CodeBook FOREIGN KEY
	(
	AccEntityL2CodeBookRef
	) REFERENCES dbo.AccEntityL2CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GetterOrPayerFactor ADD CONSTRAINT
	FK_GetterOrPayerFactor_Recorder FOREIGN KEY
	(
	RecorderRef
	) REFERENCES dbo.Users
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GetterOrPayerFactor ADD CONSTRAINT
	FK_GetterOrPayerFactor_Editor FOREIGN KEY
	(
	EditorRef
	) REFERENCES dbo.Users
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.FactorHeader ADD CONSTRAINT
	FK_FactorHeader_GetterOrPayerFactor FOREIGN KEY
	(
	GetterOrPayerFactorRef
	) REFERENCES dbo.GetterOrPayerFactor
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.FactorHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_GetterOrPayerFactor FOREIGN KEY
	(
	GetterOrPayerRef
	) REFERENCES dbo.GetterOrPayerFactor
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CashGetPay SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
