/*
   21 January 200021:10:53
   User: 
   Server: SERVER\SQLEXPRESS
   Database: ATNDBTest
   Application: 
*/

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
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.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_CashGetPayKind
GO
ALTER TABLE dbo.CashGetPayKind SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_AccDocumentHeader
GO
ALTER TABLE dbo.AccDocumentHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_UsersEditor
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_UsersRecorder
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_UsersVerifier
GO
ALTER TABLE dbo.Users SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_GetterOrPayerFactor
GO
ALTER TABLE dbo.GetterOrPayerFactor SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_CreditAccEntityL2CodeBook
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_DebitAccEntityL2CodeBook
GO
ALTER TABLE dbo.AccEntityL2CodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_CreditAccEntityL1CodeBook
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_DebitAccEntityL1CodeBook
GO
ALTER TABLE dbo.AccEntityL1CodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_CreditAccAdjutantL1CodeBook
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_DebitAccAdjutantL1CodeBook
GO
ALTER TABLE dbo.AccAdjutantL1CodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_CreditAccGeneralCodeBook
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_DebitAccGeneralCodeBook
GO
ALTER TABLE dbo.AccGeneralCodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_CreditAccGroupCodeBook
GO
ALTER TABLE dbo.CashGetPay
	DROP CONSTRAINT FK_CashGetPay_DebitAccGroupCodeBook
GO
ALTER TABLE dbo.AccGroupCodeBook SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_CashGetPay
	(
	Serial bigint NOT NULL,
	Code bigint NULL,
	AccDocumentHeaderRef bigint NULL,
	GetterOrPayerRef bigint NULL,
	CashGetPayKindRef smallint NULL,
	Detail varchar(200) NULL,
	EffectiveDate char(10) NULL,
	RecorderRef int NULL,
	RecordDate char(10) NULL,
	EditorRef int NULL,
	EditDate char(10) NULL,
	VerifierRef int NULL,
	VerificationDate char(10) NULL,
	Amount bigint NULL,
	BankAccountRef int NULL,
	ReceiptNo varchar(100) NULL,
	Description varchar(300) NULL,
	DebitAccGroupCodeBookRef bigint NULL,
	DebitAccGeneralCodeBookRef bigint NULL,
	DebitAccAdjutantL1CodeBookRef bigint NULL,
	DebitAccEntityL1CodeBookRef bigint NULL,
	DebitAccEntityL2CodeBookRef bigint NULL,
	CreditAccGroupCodeBookRef bigint NULL,
	CreditAccGeneralCodeBookRef bigint NULL,
	CreditAccAdjutantL1CodeBookRef bigint NULL,
	CreditAccEntityL1CodeBookRef bigint NULL,
	CreditAccEntityL2CodeBookRef bigint NULL,
	OfficeDetailRef int NULL,
	OfficePeriodRef int NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_CashGetPay SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.CashGetPay)
	 EXEC('INSERT INTO dbo.Tmp_CashGetPay (Serial, Code, AccDocumentHeaderRef, GetterOrPayerRef, CashGetPayKindRef, Detail, EffectiveDate, RecorderRef, RecordDate, EditorRef, EditDate, VerifierRef, VerificationDate, Amount, Description, DebitAccGroupCodeBookRef, DebitAccGeneralCodeBookRef, DebitAccAdjutantL1CodeBookRef, DebitAccEntityL1CodeBookRef, DebitAccEntityL2CodeBookRef, CreditAccGroupCodeBookRef, CreditAccGeneralCodeBookRef, CreditAccAdjutantL1CodeBookRef, CreditAccEntityL1CodeBookRef, CreditAccEntityL2CodeBookRef, OfficeDetailRef, OfficePeriodRef)
		SELECT Serial, Code, AccDocumentHeaderRef, GetterOrPayerRef, CashGetPayKindRef, Detail, EffectiveDate, RecorderRef, RecordDate, EditorRef, EditDate, VerifierRef, VerificationDate, Amount, Description, DebitAccGroupCodeBookRef, DebitAccGeneralCodeBookRef, DebitAccAdjutantL1CodeBookRef, DebitAccEntityL1CodeBookRef, DebitAccEntityL2CodeBookRef, CreditAccGroupCodeBookRef, CreditAccGeneralCodeBookRef, CreditAccAdjutantL1CodeBookRef, CreditAccEntityL1CodeBookRef, CreditAccEntityL2CodeBookRef, OfficeDetailRef, OfficePeriodRef FROM dbo.CashGetPay WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.CashGetPay
GO
EXECUTE sp_rename N'dbo.Tmp_CashGetPay', N'CashGetPay', 'OBJECT' 
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	PK_CashGetPay 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.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_CreditAccGroupCodeBook FOREIGN KEY
	(
	CreditAccGroupCodeBookRef
	) REFERENCES dbo.AccGroupCodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_CreditAccGeneralCodeBook FOREIGN KEY
	(
	CreditAccGeneralCodeBookRef
	) REFERENCES dbo.AccGeneralCodeBook
	(
	serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_CreditAccAdjutantL1CodeBook FOREIGN KEY
	(
	CreditAccAdjutantL1CodeBookRef
	) REFERENCES dbo.AccAdjutantL1CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_CreditAccEntityL1CodeBook FOREIGN KEY
	(
	CreditAccEntityL1CodeBookRef
	) REFERENCES dbo.AccEntityL1CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_CreditAccEntityL2CodeBook FOREIGN KEY
	(
	CreditAccEntityL2CodeBookRef
	) REFERENCES dbo.AccEntityL2CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_DebitAccGroupCodeBook FOREIGN KEY
	(
	DebitAccGroupCodeBookRef
	) REFERENCES dbo.AccGroupCodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_DebitAccGeneralCodeBook FOREIGN KEY
	(
	DebitAccGeneralCodeBookRef
	) REFERENCES dbo.AccGeneralCodeBook
	(
	serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_DebitAccAdjutantL1CodeBook FOREIGN KEY
	(
	DebitAccAdjutantL1CodeBookRef
	) REFERENCES dbo.AccAdjutantL1CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_DebitAccEntityL1CodeBook FOREIGN KEY
	(
	DebitAccEntityL1CodeBookRef
	) REFERENCES dbo.AccEntityL1CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_DebitAccEntityL2CodeBook FOREIGN KEY
	(
	DebitAccEntityL2CodeBookRef
	) REFERENCES dbo.AccEntityL2CodeBook
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
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 ADD CONSTRAINT
	FK_CashGetPay_UsersEditor FOREIGN KEY
	(
	EditorRef
	) REFERENCES dbo.Users
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_UsersRecorder FOREIGN KEY
	(
	RecorderRef
	) REFERENCES dbo.Users
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_UsersVerifier FOREIGN KEY
	(
	VerifierRef
	) REFERENCES dbo.Users
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_AccDocumentHeader FOREIGN KEY
	(
	AccDocumentHeaderRef
	) REFERENCES dbo.AccDocumentHeader
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CashGetPay ADD CONSTRAINT
	FK_CashGetPay_CashGetPayKind FOREIGN KEY
	(
	CashGetPayKindRef
	) REFERENCES dbo.CashGetPayKind
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
