/*
   چهارشنبه, 10 دی 139906:11:19 ب.ظ
   User: 
   Server: TAHERIAN
   Database: ATNDB
   Application: 
*/

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_AST_AssetDocDetail_OldEmployeeRef')
Begin
	ALTER TABLE AST.AssetDocDetail DROP CONSTRAINT FK_AST_AssetDocDetail_OldEmployeeRef
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_AST_AssetDocDetail_NewEmployeeRef')
Begin
	ALTER TABLE AST.AssetDocDetail DROP CONSTRAINT FK_AST_AssetDocDetail_NewEmployeeRef
End
GO

ALTER TABLE PAY.Employee SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_AST_AssetDocDetail_OldLocationRef')
Begin
	ALTER TABLE AST.AssetDocDetail DROP CONSTRAINT FK_AST_AssetDocDetail_OldLocationRef
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_AST_AssetDocDetail_NewLocationRef')
Begin
	ALTER TABLE AST.AssetDocDetail DROP CONSTRAINT FK_AST_AssetDocDetail_NewLocationRef
End
GO

ALTER TABLE PAY.JobLocation SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_AST_AssetDocDetail_OldCostCenterRef')
Begin
	ALTER TABLE AST.AssetDocDetail DROP CONSTRAINT FK_AST_AssetDocDetail_OldCostCenterRef
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_AST_AssetDocDetail_NewCostCenterRef')
Begin
	ALTER TABLE AST.AssetDocDetail DROP CONSTRAINT FK_AST_AssetDocDetail_NewCostCenterRef
End
GO

ALTER TABLE dbo.CostCenter SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE AST.AssetDocDetail
	DROP CONSTRAINT FK_AST_AssetDocDetail_AssetRef
GO
ALTER TABLE AST.Asset SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE AST.AssetDocDetail
	DROP CONSTRAINT FK_AST_AssetDocDetail_AssetDocHeaderRef
GO
ALTER TABLE AST.AssetDocHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE AST.Tmp_AssetDocDetail
	(
	Serial bigint NOT NULL IDENTITY (1, 1),
	AssetDocHeaderRef bigint NOT NULL,
	AssetRef bigint NOT NULL,
	BuyUnitPrice money NULL,
	Depreciation money NULL,
	AccumulatedDepreciation money NULL,
	OldAccumulatedDepreciation money NULL,
	BookValue money NULL,
	OldBookValue money NULL,
	ResidualValue money NULL,
	OldResidualValue money NULL,
	ExpansionOrOverhaul money NULL,
	TakeOutValue money NULL,
	ProfitOrLoss money NULL,
	BuyDate varchar(10) NULL,
	OperationDate varchar(10) NULL,
	ExpansionOrOverhaulDate varchar(10) NULL,
	TakeOutDate varchar(10) NULL,
	OldCostCenterRef bigint NULL,
	NewCostCenterRef bigint NULL,
	OldLocationRef bigint NULL,
	NewLocationRef bigint NULL,
	OldEmployeeRef bigint NULL,
	NewEmployeeRef bigint NULL,
	OldAssetPlate varchar(MAX) NULL,
	NewAssetPlate varchar(MAX) NULL,
	Tag varchar(50) NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE AST.Tmp_AssetDocDetail SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT AST.Tmp_AssetDocDetail ON
GO
IF EXISTS(SELECT * FROM AST.AssetDocDetail)
	 EXEC('INSERT INTO AST.Tmp_AssetDocDetail (Serial, AssetDocHeaderRef, AssetRef, BuyUnitPrice, Depreciation, AccumulatedDepreciation, BookValue, OldBookValue, ResidualValue, OldResidualValue, ExpansionOrOverhaul, TakeOutValue, ProfitOrLoss, BuyDate, OperationDate, ExpansionOrOverhaulDate, TakeOutDate, OldCostCenterRef, NewCostCenterRef, OldLocationRef, NewLocationRef, OldEmployeeRef, NewEmployeeRef, OldAssetPlate, NewAssetPlate, Tag)
		SELECT Serial, AssetDocHeaderRef, AssetRef, BuyUnitPrice, Depreciation, AccumulatedDepreciation, BookValue, OldBookValue, ResidualValue, OldResidualValue, ExpansionOrOverhaul, TakeOutValue, ProfitOrLoss, BuyDate, OperationDate, ExpansionOrOverhaulDate, TakeOutDate, OldCostCenterRef, NewCostCenterRef, OldLocationRef, NewLocationRef, OldEmployeeRef, NewEmployeeRef, OldAssetPlate, NewAssetPlate, Tag FROM AST.AssetDocDetail WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT AST.Tmp_AssetDocDetail OFF
GO
ALTER TABLE AST.AssetDocAccCode
	DROP CONSTRAINT FK_AST_AssetDocAccCode_AssetDocDetailRef
GO
DROP TABLE AST.AssetDocDetail
GO
EXECUTE sp_rename N'AST.Tmp_AssetDocDetail', N'AssetDocDetail', 'OBJECT' 
GO
ALTER TABLE AST.AssetDocDetail ADD CONSTRAINT
	PK_AST_AssetDocDetail PRIMARY KEY CLUSTERED 
	(
	Serial
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX IDX_AST_AssetDocDetail_AssetDocHeaderRef ON AST.AssetDocDetail
	(
	AssetDocHeaderRef
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IDX_AST_AssetDocDetail_AssetRef ON AST.AssetDocDetail
	(
	AssetRef
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE AST.AssetDocDetail ADD CONSTRAINT
	FK_AST_AssetDocDetail_AssetDocHeaderRef FOREIGN KEY
	(
	AssetDocHeaderRef
	) REFERENCES AST.AssetDocHeader
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.AssetDocDetail ADD CONSTRAINT
	FK_AST_AssetDocDetail_AssetRef FOREIGN KEY
	(
	AssetRef
	) REFERENCES AST.Asset
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.AssetDocDetail ADD CONSTRAINT
	FK_AST_AssetDocDetail_OldCostCenterRef FOREIGN KEY
	(
	OldCostCenterRef
	) REFERENCES dbo.CostCenter
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.AssetDocDetail ADD CONSTRAINT
	FK_AST_AssetDocDetail_NewCostCenterRef FOREIGN KEY
	(
	NewCostCenterRef
	) REFERENCES dbo.CostCenter
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.AssetDocDetail ADD CONSTRAINT
	FK_AST_AssetDocDetail_OldLocationRef FOREIGN KEY
	(
	OldLocationRef
	) REFERENCES PAY.JobLocation
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.AssetDocDetail ADD CONSTRAINT
	FK_AST_AssetDocDetail_NewLocationRef FOREIGN KEY
	(
	NewLocationRef
	) REFERENCES PAY.JobLocation
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.AssetDocDetail ADD CONSTRAINT
	FK_AST_AssetDocDetail_OldEmployeeRef FOREIGN KEY
	(
	OldEmployeeRef
	) REFERENCES PAY.Employee
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.AssetDocDetail ADD CONSTRAINT
	FK_AST_AssetDocDetail_NewEmployeeRef FOREIGN KEY
	(
	NewEmployeeRef
	) REFERENCES PAY.Employee
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE AST.AssetDocAccCode ADD CONSTRAINT
	FK_AST_AssetDocAccCode_AssetDocDetailRef FOREIGN KEY
	(
	AssetDocDetailRef
	) REFERENCES AST.AssetDocDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.AssetDocAccCode SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
