Use ATNDBTest
GO

Create Table GritFactorKind(
	Serial Int Not Null,
	FA_Des Varchar(50) Null,
	EN_Des Varchar(50) Null)
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.GritFactor
	DROP CONSTRAINT FK_GritFactor_OfficePeriod
GO
ALTER TABLE dbo.OfficePeriod SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GritFactor
	DROP CONSTRAINT FK_GritFactor_OfficeDetail
GO
ALTER TABLE dbo.OfficeDetail SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GritFactor
	DROP CONSTRAINT FK_GritFactor_Recorder
GO
ALTER TABLE dbo.GritFactor
	DROP CONSTRAINT FK_GritFactor_Editor
GO
ALTER TABLE dbo.GritFactor
	DROP CONSTRAINT FK_GritFactor_Verifier
GO
ALTER TABLE dbo.Users SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GritFactor
	DROP CONSTRAINT FK_GritFactor_GritProduct
GO
ALTER TABLE dbo.GritProduct SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GritFactor
	DROP CONSTRAINT FK_GritFactor_GritCustomer
GO
ALTER TABLE dbo.GritCustomer SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GritFactor
	DROP CONSTRAINT FK_GritFactor_AccDocumentHeader_OnVerification
GO
ALTER TABLE dbo.AccDocumentHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_GritFactor
	(
	Serial bigint NOT NULL,
	Code bigint NULL,
	AccDocumentHeaderRefOnVerification bigint NULL,
	GritCustomerRef bigint NULL,
	GritFactorKindRef int NULL,
	ControlNo varchar(20) NULL,
	EffectiveDate char(10) NULL,
	EffectiveTime char(8) NULL,
	LicensePlate varchar(15) NULL,
	EmptyWeight bigint NULL,
	GrossWeight bigint NULL,
	GritProductRef bigint NULL,
	Quantity float(53) NULL,
	UnitPrice bigint NULL,
	ApprovedUnitPrice bigint NULL,
	TotalPrice bigint NULL,
	BuyUnitPriceInProductTable bigint NULL,
	VAT bigint NULL,
	Cost bigint NULL,
	TotalPaid bigint NULL,
	PayDate char(10) NULL,
	RecorderRef int NULL,
	RecordDate char(10) NULL,
	EditorRef int NULL,
	EditDate char(10) NULL,
	VerifierRef int NULL,
	VerificationDate char(10) NULL,
	Description varchar(300) NULL,
	OfficeDetailRef int NULL,
	OfficePeriodRef int NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_GritFactor SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.GritFactor)
	 EXEC('INSERT INTO dbo.Tmp_GritFactor (Serial, Code, AccDocumentHeaderRefOnVerification, GritCustomerRef, ControlNo, EffectiveDate, EffectiveTime, LicensePlate, EmptyWeight, GrossWeight, GritProductRef, Quantity, UnitPrice, ApprovedUnitPrice, TotalPrice, BuyUnitPriceInProductTable, VAT, Cost, TotalPaid, PayDate, RecorderRef, RecordDate, EditorRef, EditDate, VerifierRef, VerificationDate, Description, OfficeDetailRef, OfficePeriodRef)
		SELECT Serial, Code, AccDocumentHeaderRefOnVerification, GritCustomerRef, ControlNo, EffectiveDate, EffectiveTime, LicensePlate, EmptyWeight, GrossWeight, GritProductRef, Quantity, UnitPrice, ApprovedUnitPrice, TotalPrice, BuyUnitPriceInProductTable, VAT, Cost, TotalPaid, PayDate, RecorderRef, RecordDate, EditorRef, EditDate, VerifierRef, VerificationDate, Description, OfficeDetailRef, OfficePeriodRef FROM dbo.GritFactor WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.GritFactorAccCode
	DROP CONSTRAINT FK_GritFactorAccCode_GritFactor
GO
DROP TABLE dbo.GritFactor
GO
EXECUTE sp_rename N'dbo.Tmp_GritFactor', N'GritFactor', 'OBJECT' 
GO
ALTER TABLE dbo.GritFactor ADD CONSTRAINT
	PK_GritFactor 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_GritFactor_GritCustomerRef ON dbo.GritFactor
	(
	GritCustomerRef
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IDX_GritFactor_EffectiveDate ON dbo.GritFactor
	(
	EffectiveDate
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IDX_GritFactor_GritProductRef ON dbo.GritFactor
	(
	GritProductRef
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.GritFactor ADD CONSTRAINT
	FK_GritFactor_AccDocumentHeader_OnVerification FOREIGN KEY
	(
	AccDocumentHeaderRefOnVerification
	) REFERENCES dbo.AccDocumentHeader
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GritFactor ADD CONSTRAINT
	FK_GritFactor_GritCustomer FOREIGN KEY
	(
	GritCustomerRef
	) REFERENCES dbo.GritCustomer
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GritFactor ADD CONSTRAINT
	FK_GritFactor_GritProduct FOREIGN KEY
	(
	GritProductRef
	) REFERENCES dbo.GritProduct
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GritFactor ADD CONSTRAINT
	FK_GritFactor_Recorder FOREIGN KEY
	(
	RecorderRef
	) REFERENCES dbo.Users
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GritFactor ADD CONSTRAINT
	FK_GritFactor_Editor FOREIGN KEY
	(
	EditorRef
	) REFERENCES dbo.Users
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GritFactor ADD CONSTRAINT
	FK_GritFactor_Verifier FOREIGN KEY
	(
	VerifierRef
	) REFERENCES dbo.Users
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GritFactor ADD CONSTRAINT
	FK_GritFactor_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
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
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GritFactorAccCode ADD CONSTRAINT
	FK_GritFactorAccCode_GritFactor FOREIGN KEY
	(
	GritFactorRef
	) REFERENCES dbo.GritFactor
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GritFactorAccCode SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
