Use ATNDBTest
Go

-- MonthlyFactorPerEmployee در جدول MonthIndexRef تغییر نام ستون
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
EXECUTE sp_rename N'PAY.MonthlyFactorPerEmployee.Month', N'Tmp_MonthIndexRef', 'COLUMN' 
GO
EXECUTE sp_rename N'PAY.MonthlyFactorPerEmployee.Tmp_MonthIndexRef', N'MonthIndexRef', 'COLUMN' 
GO
ALTER TABLE PAY.MonthlyFactorPerEmployee SET (LOCK_ESCALATION = TABLE)
GO
COMMIT



-- PaymentComputingDetail به جدول FactorRef اضافه کردن ستون
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 PAY.PaymentComputingDetail
	DROP CONSTRAINT FK_PAY_PaymentComputingDetail_AccEntityL2CodeBook_Credit
GO
ALTER TABLE PAY.PaymentComputingDetail
	DROP CONSTRAINT FK_PAY_PaymentComputingDetail_AccEntityL2CodeBook_Debit
GO
ALTER TABLE dbo.AccEntityL2CodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.PaymentComputingDetail
	DROP CONSTRAINT FK_PAY_PaymentComputingDetail_AccEntityL1CodeBook_Credit
GO
ALTER TABLE PAY.PaymentComputingDetail
	DROP CONSTRAINT FK_PAY_PaymentComputingDetail_AccEntityL1CodeBook_Debit
GO
ALTER TABLE dbo.AccEntityL1CodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.PaymentComputingDetail
	DROP CONSTRAINT FK_PAY_PaymentComputingDetail_AccAdjutantL1CodeBook_Credit
GO
ALTER TABLE PAY.PaymentComputingDetail
	DROP CONSTRAINT FK_PAY_PaymentComputingDetail_AccAdjutantL1CodeBook_Debit
GO
ALTER TABLE dbo.AccAdjutantL1CodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.PaymentComputingDetail
	DROP CONSTRAINT FK_PAY_PaymentComputingDetail_Employee
GO
ALTER TABLE PAY.Employee SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.PaymentComputingDetail
	DROP CONSTRAINT FK_PAY_PaymentComputingDetail_PaymentComputingHeader
GO
ALTER TABLE PAY.PaymentComputingHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE PAY.Tmp_PaymentComputingDetail
	(
	Serial bigint NOT NULL IDENTITY (1, 1),
	PaymentComputingHeaderRef bigint NOT NULL,
	ComputingCode int NOT NULL,
	ComputingName varchar(50) NOT NULL,
	EmployeeRef bigint NULL,
	Periority int NULL,
	FactorRef bigint NULL,
	FADes varchar(300) NULL,
	ENDes varchar(300) NULL,
	DebitAccAdjutantL1CodeBookRef bigint NULL,
	DebitAccEntityL1CodeBookRef bigint NULL,
	DebitAccEntityL2CodeBookRef bigint NULL,
	CreditAccAdjutantL1CodeBookRef bigint NULL,
	CreditAccEntityL1CodeBookRef bigint NULL,
	CreditAccEntityL2CodeBookRef bigint NULL,
	Amount decimal(38, 6) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE PAY.Tmp_PaymentComputingDetail SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT PAY.Tmp_PaymentComputingDetail ON
GO
IF EXISTS(SELECT * FROM PAY.PaymentComputingDetail)
	 EXEC('INSERT INTO PAY.Tmp_PaymentComputingDetail (Serial, PaymentComputingHeaderRef, ComputingCode, ComputingName, EmployeeRef, Periority, FADes, ENDes, DebitAccAdjutantL1CodeBookRef, DebitAccEntityL1CodeBookRef, DebitAccEntityL2CodeBookRef, CreditAccAdjutantL1CodeBookRef, CreditAccEntityL1CodeBookRef, CreditAccEntityL2CodeBookRef, Amount)
		SELECT Serial, PaymentComputingHeaderRef, ComputingCode, ComputingName, EmployeeRef, Periority, FADes, ENDes, DebitAccAdjutantL1CodeBookRef, DebitAccEntityL1CodeBookRef, DebitAccEntityL2CodeBookRef, CreditAccAdjutantL1CodeBookRef, CreditAccEntityL1CodeBookRef, CreditAccEntityL2CodeBookRef, Amount FROM PAY.PaymentComputingDetail WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT PAY.Tmp_PaymentComputingDetail OFF
GO
DROP TABLE PAY.PaymentComputingDetail
GO
EXECUTE sp_rename N'PAY.Tmp_PaymentComputingDetail', N'PaymentComputingDetail', 'OBJECT' 
GO
ALTER TABLE PAY.PaymentComputingDetail ADD CONSTRAINT
	PK_PAY_PaymentComputingDetail 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 PAY.PaymentComputingDetail ADD CONSTRAINT
	FK_PAY_PaymentComputingDetail_PaymentComputingHeader FOREIGN KEY
	(
	PaymentComputingHeaderRef
	) REFERENCES PAY.PaymentComputingHeader
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.PaymentComputingDetail ADD CONSTRAINT
	FK_PAY_PaymentComputingDetail_Employee FOREIGN KEY
	(
	EmployeeRef
	) REFERENCES PAY.Employee
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.PaymentComputingDetail ADD CONSTRAINT
	FK_PAY_PaymentComputingDetail_AccAdjutantL1CodeBook_Credit FOREIGN KEY
	(
	CreditAccAdjutantL1CodeBookRef
	) REFERENCES dbo.AccAdjutantL1CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.PaymentComputingDetail ADD CONSTRAINT
	FK_PAY_PaymentComputingDetail_AccAdjutantL1CodeBook_Debit FOREIGN KEY
	(
	DebitAccAdjutantL1CodeBookRef
	) REFERENCES dbo.AccAdjutantL1CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.PaymentComputingDetail ADD CONSTRAINT
	FK_PAY_PaymentComputingDetail_AccEntityL1CodeBook_Credit FOREIGN KEY
	(
	CreditAccEntityL1CodeBookRef
	) REFERENCES dbo.AccEntityL1CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.PaymentComputingDetail ADD CONSTRAINT
	FK_PAY_PaymentComputingDetail_AccEntityL1CodeBook_Debit FOREIGN KEY
	(
	DebitAccEntityL1CodeBookRef
	) REFERENCES dbo.AccEntityL1CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.PaymentComputingDetail ADD CONSTRAINT
	FK_PAY_PaymentComputingDetail_AccEntityL2CodeBook_Credit FOREIGN KEY
	(
	CreditAccEntityL2CodeBookRef
	) REFERENCES dbo.AccEntityL2CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.PaymentComputingDetail ADD CONSTRAINT
	FK_PAY_PaymentComputingDetail_AccEntityL2CodeBook_Debit FOREIGN KEY
	(
	DebitAccEntityL2CodeBookRef
	) REFERENCES dbo.AccEntityL2CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
