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
If Exists(SELECT * FROM sys.objects WHERE name ='FK_GetterOrPayerFactor_Recorder')
Begin
	ALTER TABLE dbo.GetterOrPayerFactor
		DROP CONSTRAINT FK_GetterOrPayerFactor_Recorder
End
GO
If Exists(SELECT * FROM sys.objects WHERE name ='FK_GetterOrPayerFactor_Editor')
Begin
	ALTER TABLE dbo.GetterOrPayerFactor
		DROP CONSTRAINT FK_GetterOrPayerFactor_Editor
End
GO
ALTER TABLE dbo.Users SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
If Exists(SELECT * FROM sys.objects WHERE name ='FK_GetterOrPayerFactor_AccEntityL2CodeBook')
Begin
	ALTER TABLE dbo.GetterOrPayerFactor
		DROP CONSTRAINT FK_GetterOrPayerFactor_AccEntityL2CodeBook
End
GO
ALTER TABLE dbo.AccEntityL2CodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_GetterOrPayerFactor_AccEntityL1CodeBook')
Begin
	ALTER TABLE dbo.GetterOrPayerFactor
		DROP CONSTRAINT FK_GetterOrPayerFactor_AccEntityL1CodeBook
End
GO
ALTER TABLE dbo.AccEntityL1CodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='FK_GetterOrPayerFactor_GetterOrPayerFactorCategory')
Begin
	ALTER TABLE dbo.GetterOrPayerFactor
		DROP CONSTRAINT FK_GetterOrPayerFactor_GetterOrPayerFactorCategory
End
GO
ALTER TABLE dbo.GetterOrPayerFactorCategory SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
If Exists(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='FK_GetterOrPayerFactor_Degree')
Begin
	ALTER TABLE dbo.GetterOrPayerFactor
		DROP CONSTRAINT FK_GetterOrPayerFactor_Degree
End
GO
ALTER TABLE dbo.Degree SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
If Exists(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='FK_GetterOrPayerFactor_GetterOrPayerFactorKind')
Begin
	ALTER TABLE dbo.GetterOrPayerFactor
		DROP CONSTRAINT FK_GetterOrPayerFactor_GetterOrPayerFactorKind
End
GO
ALTER TABLE dbo.GetterOrPayerFactorKind SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
If Exists(SELECT * FROM sys.objects WHERE name ='DF_GetterOrPayerFactor_Active')
Begin
	ALTER TABLE dbo.GetterOrPayerFactor
		DROP CONSTRAINT DF_GetterOrPayerFactor_Active
End
GO
CREATE TABLE dbo.Tmp_GetterOrPayerFactor
	(
	Serial bigint NOT NULL,
	Code bigint NULL,
	GetterOrPayerFactorTypeRef smallint NULL,
	CityRef int NULL,
	Location varchar(50) NULL,
	PersonOrCompany bit NULL,
	FAFirstName varchar(50) NULL,
	ENFirstName varchar(50) NULL,
	FAName varchar(50) NULL,
	ENName varchar(50) NULL,
	KindRef smallint NOT NULL,
	GetterOrPayerFactorCategoryRef bigint 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,
	Tag varchar(50) 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, ENName, KindRef, GetterOrPayerFactorCategoryRef, 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, Tag)
		SELECT Serial, Code, FAName, ENName, KindRef, GetterOrPayerFactorCategoryRef, 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, Tag FROM dbo.GetterOrPayerFactor WITH (HOLDLOCK TABLOCKX)')
GO

If Exists(Select * From sys.foreign_keys Where parent_object_id = OBJECT_ID(N'AST.AssetDocHeader') AND object_id = OBJECT_ID(N'AST.FK_AST_AssetDocHeader_SupplierRef'))
Begin
	ALTER TABLE AST.AssetDocHeader	DROP CONSTRAINT FK_AST_AssetDocHeader_SupplierRef
End
GO

ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_GetterOrPayerFactor
GO
ALTER TABLE dbo.GritFactor
	DROP CONSTRAINT FK_GritFactor_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_GetterOrPayerFactorCategory FOREIGN KEY
	(
	GetterOrPayerFactorCategoryRef
	) REFERENCES dbo.GetterOrPayerFactorCategory
	(
	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.GritFactor ADD CONSTRAINT
	FK_GritFactor_GetterOrPayerFactor FOREIGN KEY
	(
	GetterOrPayerFactorRef
	) REFERENCES dbo.GetterOrPayerFactor
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GritFactor 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
BEGIN TRANSACTION
GO
ALTER TABLE AST.AssetDocHeader ADD CONSTRAINT
	FK_AST_AssetDocHeader_SupplierRef FOREIGN KEY
	(
	SupplierRef
	) REFERENCES dbo.GetterOrPayerFactor
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.AssetDocHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT



Update
	GetterOrPayerFactor
Set
	GetterOrPayerFactorTypeRef = 2,
	CityRef = 2,
	PersonOrCompany = 0
