Use ATNDBTest
Go

If EXISTS (Select * From sys.tables Where name = 'Factors') DROP TABLE PAY.Factors
If EXISTS (Select * From sys.tables Where name = 'Job') DROP TABLE PAY.Job
If EXISTS (Select * From sys.tables Where name = 'JobCategory') DROP TABLE PAY.JobCategory
If EXISTS (Select * From sys.tables Where name = 'JobLocation') DROP TABLE PAY.JobLocation
If EXISTS (Select * From sys.tables Where name = 'LoanType') DROP TABLE PAY.LoanType
If EXISTS (Select * From sys.tables Where name = 'SSIOrg') DROP TABLE PAY.SSIOrg
If EXISTS (Select * From sys.tables Where name = 'TaxGroup') DROP TABLE PAY.TaxGroup
If EXISTS (Select * From sys.tables Where name = 'TaxTableDetail') DROP TABLE PAY.TaxTableDetail
If EXISTS (Select * From sys.tables Where name = 'TaxTableHeader') DROP TABLE PAY.TaxTableHeader


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.OfficePeriod
	DROP CONSTRAINT FK_OfficePeriod_OfficeDetail
GO
ALTER TABLE dbo.OfficeDetail SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_OfficePeriod
	(
	Serial int NOT NULL,
	Code int NULL,
	PeriodName varchar(100) NULL,
	StartDate nchar(10) NULL,
	EndDate nchar(10) NULL,
	InaugurationAccDocumentSerial bigint NULL,
	CloseTemporaryAccountsAccDocumentSerial bigint NULL,
	ConclusionAccDocumentSerial bigint NULL,
	PreviousOfficePeriodSerial int NULL,
	RecorderRef int NULL,
	RecordDate nchar(10) NULL,
	ActivePeriod bit NULL,
	ReadOnlyPeriod bit NULL,
	Comment varchar(300) NULL,
	OfficeDetailRef int NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_OfficePeriod SET (LOCK_ESCALATION = TABLE)
GO
DECLARE @v sql_variant 
SET @v = N'دوره مالی قبلی که در آن اسناد پایان دوره انبار و حسابداری پس از صدور در آن به این دوره منتقل شده است'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_OfficePeriod', N'COLUMN', N'PreviousOfficePeriodSerial'
GO
IF EXISTS(SELECT * FROM dbo.OfficePeriod)
	 EXEC('INSERT INTO dbo.Tmp_OfficePeriod (Serial, Code, PeriodName, StartDate, EndDate, InaugurationAccDocumentSerial, ConclusionAccDocumentSerial, PreviousOfficePeriodSerial, RecorderRef, RecordDate, ActivePeriod, ReadOnlyPeriod, Comment, OfficeDetailRef)
		SELECT Serial, Code, PeriodName, StartDate, EndDate, InaugurationAccDocumentSerial, ConclusionAccDocumentSerial, PreviousOfficePeriodSerial, RecorderRef, RecordDate, ActivePeriod, ReadOnlyPeriod, Comment, OfficeDetailRef FROM dbo.OfficePeriod WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.SystemParametersPerOfficePeriod
	DROP CONSTRAINT FK_SystemParametersPerOfficePeriod_OfficePeriod
GO
ALTER TABLE dbo.CostHeader
	DROP CONSTRAINT FK_CostHeader_OfficePeriod
GO
ALTER TABLE dbo.GritFactor
	DROP CONSTRAINT FK_GritFactor_OfficePeriod
GO
DROP TABLE dbo.OfficePeriod
GO
EXECUTE sp_rename N'dbo.Tmp_OfficePeriod', N'OfficePeriod', 'OBJECT' 
GO
ALTER TABLE dbo.OfficePeriod ADD CONSTRAINT
	PK_OfficePeriod 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.OfficePeriod ADD CONSTRAINT
	FK_OfficePeriod_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GritFactor ADD CONSTRAINT
	FK_GritFactor_OfficePeriod FOREIGN KEY
	(
	OfficePeriodRef
	) REFERENCES dbo.OfficePeriod
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GritFactor SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

GO

BEGIN TRANSACTION
GO
ALTER TABLE dbo.CostHeader ADD CONSTRAINT
	FK_CostHeader_OfficePeriod FOREIGN KEY
	(
	OfficePeriodRef
	) REFERENCES dbo.OfficePeriod
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CostHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.SystemParametersPerOfficePeriod ADD CONSTRAINT
	FK_SystemParametersPerOfficePeriod_OfficePeriod FOREIGN KEY
	(
	OfficePeriodRef
	) REFERENCES dbo.OfficePeriod
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.SystemParametersPerOfficePeriod SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
