
Use ATNDBTest
Go


/*
   چهارشنبه, 21 اسفند 139801:38:05 ب.ظ
   User: 
   Server: TAHERIAN
   Database: ATNDB
   Application: 
*/

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
CREATE TABLE dbo.Tmp_OfficeDetail
	(
	Serial int NOT NULL,
	Code int NULL,
	OfficeName varchar(200) NULL,
	Phone1 varchar(50) NULL,
	Phone2 varchar(50) NULL,
	Fax varchar(50) NULL,
	Address varchar(300) NULL,
	PostCode varchar(20) NULL,
	RegisterationDate char(10) NULL,
	RegisterationNo varchar(50) NULL,
	EconomicCode varchar(50) NULL,
	SSN varchar(50) NULL,
	ActiveOffice bit NULL,
	RecorderRef int NULL,
	RecordDate char(10) NULL,
	comment varchar(300) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_OfficeDetail SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.OfficeDetail)
	 EXEC('INSERT INTO dbo.Tmp_OfficeDetail (Serial, Code, OfficeName, Phone1, Phone2, Fax, Address, PostCode, RegisterationDate, RegisterationNo, EconomicCode, ActiveOffice, RecorderRef, RecordDate, comment)
		SELECT Serial, Code, OfficeName, Phone1, Phone2, Fax, Address, PostCode, RegisterationDate, RegisterationNo, EconomicCode, ActiveOffice, RecorderRef, RecordDate, comment FROM dbo.OfficeDetail WITH (HOLDLOCK TABLOCKX)')
GO



If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_Contract_OfficeDetail')
Begin
	ALTER TABLE PAY.Contract DROP CONSTRAINT FK_PAY_Contract_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_SSIOrg_OfficeDetail')
Begin
	ALTER TABLE PAY.SSIOrg DROP CONSTRAINT FK_PAY_SSIOrg_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_GritProduct_OfficeDetail')
Begin
	ALTER TABLE dbo.GritProduct DROP CONSTRAINT FK_GritProduct_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_SPIOrg_OfficeDetail')
Begin
	ALTER TABLE PAY.SPIOrg DROP CONSTRAINT FK_PAY_SPIOrg_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_MonthlyFactorPerEmployee_OfficeDetail')
Begin
	ALTER TABLE PAY.MonthlyFactorPerEmployee DROP CONSTRAINT FK_PAY_MonthlyFactorPerEmployee_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_Broker_OfficeDetail')
Begin
	ALTER TABLE dbo.Broker DROP CONSTRAINT FK_Broker_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_EmployeeTaxOrg_OfficeDetail')
Begin
	ALTER TABLE PAY.EmployeeTaxOrg DROP CONSTRAINT FK_PAY_EmployeeTaxOrg_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_PaymentComputingHeader_OfficeDetail')
Begin
	ALTER TABLE PAY.PaymentComputingHeader DROP CONSTRAINT FK_PAY_PaymentComputingHeader_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_JobCategory_OfficeDetail')
Begin
	ALTER TABLE PAY.JobCategory DROP CONSTRAINT FK_PAY_JobCategory_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_GritFactor_OfficeDetail')
Begin
	ALTER TABLE dbo.GritFactor DROP CONSTRAINT FK_GritFactor_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_FactorCategory_OfficeDetail')
Begin
	ALTER TABLE dbo.FactorCategory DROP CONSTRAINT FK_FactorCategory_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_Job_OfficeDetail')
Begin
	ALTER TABLE PAY.Job DROP CONSTRAINT FK_PAY_Job_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_JobLocation_OfficeDetail')
Begin
	ALTER TABLE PAY.JobLocation DROP CONSTRAINT FK_PAY_JobLocation_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_TaxGroup_OfficeDetail')
Begin
	ALTER TABLE PAY.TaxGroup DROP CONSTRAINT FK_PAY_TaxGroup_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_UnitWeight_OfficeDetail')
Begin
	ALTER TABLE dbo.UnitWeight DROP CONSTRAINT FK_UnitWeight_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_LoanPaybackPlanHeader_OfficeDetail')
Begin
	ALTER TABLE PAY.LoanPaybackPlanHeader DROP CONSTRAINT FK_PAY_LoanPaybackPlanHeader_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_TaxTableHeader_OfficeDetail')
Begin
	ALTER TABLE PAY.TaxTableHeader DROP CONSTRAINT FK_PAY_TaxTableHeader_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_LoanType_OfficeDetail')
Begin
	ALTER TABLE PAY.LoanType DROP CONSTRAINT FK_PAY_LoanType_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_OfficePeriod_OfficeDetail')
Begin
	ALTER TABLE dbo.OfficePeriod DROP CONSTRAINT FK_OfficePeriod_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_CostBase_OfficeDetail')
Begin
	ALTER TABLE dbo.CostBase DROP CONSTRAINT FK_CostBase_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_SystemParametersPerOffice_OfficeDetail')
Begin
	ALTER TABLE dbo.SystemParametersPerOfficeDetail DROP CONSTRAINT FK_SystemParametersPerOffice_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_CostCenter_OfficeDetail')
Begin
	ALTER TABLE dbo.CostCenter DROP CONSTRAINT FK_CostCenter_OfficeDetail
End
GO

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

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_Factor_OfficeDetail')
Begin
	ALTER TABLE PAY.Factor DROP CONSTRAINT FK_PAY_Factor_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_CostCash_OfficeDetail')
Begin
	ALTER TABLE dbo.CostCash DROP CONSTRAINT FK_CostCash_OfficeDetail
End
GO

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

If Exists(SELECT * FROM sys.objects WHERE name ='FK_CostFunds_OfficeDetail')
Begin
	ALTER TABLE dbo.CostFunds DROP CONSTRAINT FK_CostFunds_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_Employee_OfficeDetail')
Begin
	ALTER TABLE PAY.Employee DROP CONSTRAINT FK_PAY_Employee_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_CostCredit_OfficeDetail')
Begin
	ALTER TABLE dbo.CostCredit DROP CONSTRAINT FK_CostCredit_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_SystemAccCodeDefaultPerOffice_OfficeDetail')
Begin
	ALTER TABLE dbo.SystemAccCodeDefaultPerOffice DROP CONSTRAINT FK_SystemAccCodeDefaultPerOffice_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_CostHeader_OfficeDetail')
Begin
	ALTER TABLE dbo.CostHeader DROP CONSTRAINT FK_CostHeader_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_CostFunds_OfficeDetail')
Begin
	ALTER TABLE dbo.CostFunds DROP CONSTRAINT FK_CostFunds_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_OfficePeriod_OfficeDetail')
Begin
	ALTER TABLE dbo.OfficePeriod DROP CONSTRAINT FK_OfficePeriod_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_CostCredit_OfficeDetail')
Begin
	ALTER TABLE dbo.CostCredit DROP CONSTRAINT FK_CostCredit_OfficeDetail
End
GO

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

If Exists(SELECT * FROM sys.objects WHERE name ='FK_SystemParametersPerOffice_OfficeDetail')
Begin
	ALTER TABLE dbo.SystemParametersPerOfficeDetail DROP CONSTRAINT FK_SystemParametersPerOffice_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_CostHeader_OfficeDetail')
Begin
	ALTER TABLE dbo.CostHeader DROP CONSTRAINT FK_CostHeader_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_GritProduct_OfficeDetail')
Begin
	ALTER TABLE dbo.GritProduct DROP CONSTRAINT FK_GritProduct_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_Broker_OfficeDetail')
Begin
	ALTER TABLE dbo.Broker DROP CONSTRAINT FK_Broker_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_GritFactor_OfficeDetail')
Begin
	ALTER TABLE dbo.GritFactor DROP CONSTRAINT FK_GritFactor_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_SystemAccCodeDefaultPerOffice_OfficeDetail')
Begin
	ALTER TABLE dbo.SystemAccCodeDefaultPerOffice DROP CONSTRAINT FK_SystemAccCodeDefaultPerOffice_OfficeDetail
End
GO

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

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_Factors_OfficeDetail')
Begin
	ALTER TABLE PAY.Factors DROP CONSTRAINT FK_PAY_Factors_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_MonthlyFactorPerEmployee_OfficeDetail')
Begin
	ALTER TABLE PAY.MonthlyFactorPerEmployee DROP CONSTRAINT FK_PAY_MonthlyFactorPerEmployee_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_CostBase_OfficeDetail')
Begin
	ALTER TABLE dbo.CostBase DROP CONSTRAINT FK_CostBase_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_PAY_PaymentComputingHeader_OfficeDetail')
Begin
	ALTER TABLE PAY.PaymentComputingHeader DROP CONSTRAINT FK_PAY_PaymentComputingHeader_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_CostCenter_OfficeDetail')
Begin
	ALTER TABLE dbo.CostCenter DROP CONSTRAINT FK_CostCenter_OfficeDetail
End
GO

If Exists(SELECT * FROM sys.objects WHERE name ='FK_CostCash_OfficeDetail')
Begin
	ALTER TABLE dbo.CostCash DROP CONSTRAINT FK_CostCash_OfficeDetail
End
GO

DROP TABLE dbo.OfficeDetail
GO
EXECUTE sp_rename N'dbo.Tmp_OfficeDetail', N'OfficeDetail', 'OBJECT' 
GO
ALTER TABLE dbo.OfficeDetail ADD CONSTRAINT
	PK_OfficeDetail PRIMARY KEY CLUSTERED 
	(
	Serial
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CostHeader ADD CONSTRAINT
	FK_CostHeader_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	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.SystemAccCodeDefaultPerOffice ADD CONSTRAINT
	FK_SystemAccCodeDefaultPerOffice_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.SystemAccCodeDefaultPerOffice SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CostCredit ADD CONSTRAINT
	FK_CostCredit_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CostCredit SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.Employee ADD CONSTRAINT
	FK_PAY_Employee_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.Employee SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CostFunds ADD CONSTRAINT
	FK_CostFunds_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CostFunds SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE AST.Asset ADD CONSTRAINT
	FK_AST_Asset_OfficeDetailRef FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.Asset SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CostCash ADD CONSTRAINT
	FK_CostCash_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CostCash SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.Factor ADD CONSTRAINT
	FK_PAY_Factor_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.Factor SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE AST.AssetDocHeader ADD CONSTRAINT
	FK_AST_AssetDocHeader_OfficeDetailRef FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE AST.AssetDocHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CostCenter ADD CONSTRAINT
	FK_CostCenter_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CostCenter SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.SystemParametersPerOfficeDetail ADD CONSTRAINT
	FK_SystemParametersPerOffice_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.SystemParametersPerOfficeDetail SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CostBase ADD CONSTRAINT
	FK_CostBase_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.CostBase SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
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
ALTER TABLE dbo.OfficePeriod SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.LoanType ADD CONSTRAINT
	FK_PAY_LoanType_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.LoanType SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.TaxTableHeader ADD CONSTRAINT
	FK_PAY_TaxTableHeader_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.TaxTableHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO

If Not Exists(SELECT * FROM sys.objects WHERE name ='LoanPaybackPlanHeader')
Begin
	Create Table PAY.LoanPaybackPlanHeader(
		Serial                            Bigint Identity(1,1) Not Null, 
		Code                              Bigint NULL, ------------------
		LoanTypeRef		                  Bigint NULL, ------------------
		EmployeeRef                       Bigint Not Null, --------------

		EffectiveDate                     Char(10) Null, ----------------
		InstallmentStartDate              Char(10) Null, ----------------
		InstallmentEndDate                Char(10) Null, ----------------
		InstallmentDuration               Smallint Null, ----------------
		LoanDuration                      Int Null, ---------------------

		LoanAmount		                  Bigint NULL, ------------------
		WagePercent                       Decimal(7,4) NULL, ------------
		WageAmount                        Bigint NULL, ------------------
		InterestPercent                   Decimal(7,4) NULL, ------------
		InterestAmount                    Bigint NULL, ------------------
		FinedPercent                      Decimal(7,4) NULL, ------------
		FinedAmount                       Bigint NULL, ------------------

		TotalAmount                       Bigint NULL, ------------------
		PaidAmount                        Bigint NULL, ------------------
		RemainAmount                      Bigint NULL, ------------------

		TotalInstallment				  Int Null, ---------------------
		PaidInstallment		  		      Int Null, ---------------------
		RemainInstallment				  Int Null, ---------------------

		BorrowerAccAdjutantL1CodeBookRef  Bigint Null, ------------------
		SourceAccAdjutantL1CodeBookRef    Bigint Null, ------------------
		WageAccAdjutantL1CodeBookRef      Bigint Null, ------------------
		InterestAccAdjutantL1CodeBookRef  Bigint Null, ------------------
		FinedAccAdjutantL1CodeBookRef     Bigint Null, ------------------

		InstallmentRoundDigit		      Bigint NULL, ------------------
		InstallmentRound_FirstOrLast      Bigint NULL, ------------------

		Comment                           Varchar(300) Null, ------------
		RecorderRef                       Int Null, ---------------------
		RecordDate                        Char(10) Null, ----------------
		EditorRef                         Int Null, ---------------------
		EditDate                          char(10) Null,	-------------
		VerifierRef                       Int Null, ---------------------
		VerificationDate                  Char(10) Null, ----------------
		OfficeDetailRef                   Int Null, ---------------------
		RowVersion                        DateTime)

	Alter Table PAY.LoanPaybackPlanHeader Add Constraint PK_PAY_LoanPaybackPlanHeader Primary Key Clustered (Serial)
End

ALTER TABLE PAY.LoanPaybackPlanHeader ADD CONSTRAINT
	FK_PAY_LoanPaybackPlanHeader_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.LoanPaybackPlanHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.UnitWeight ADD CONSTRAINT
	FK_UnitWeight_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.UnitWeight SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.TaxGroup ADD CONSTRAINT
	FK_PAY_TaxGroup_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.TaxGroup SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.JobLocation ADD CONSTRAINT
	FK_PAY_JobLocation_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.JobLocation SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.Job ADD CONSTRAINT
	FK_PAY_Job_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.Job SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.FactorCategory ADD CONSTRAINT
	FK_FactorCategory_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.FactorCategory SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
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 SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.JobCategory ADD CONSTRAINT
	FK_PAY_JobCategory_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.JobCategory SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.PaymentComputingHeader ADD CONSTRAINT
	FK_PAY_PaymentComputingHeader_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.PaymentComputingHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.EmployeeTaxOrg ADD CONSTRAINT
	FK_PAY_EmployeeTaxOrg_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.EmployeeTaxOrg SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Broker ADD CONSTRAINT
	FK_Broker_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Broker SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.MonthlyFactorPerEmployee ADD CONSTRAINT
	FK_PAY_MonthlyFactorPerEmployee_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.MonthlyFactorPerEmployee SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.SPIOrg ADD CONSTRAINT
	FK_PAY_SPIOrg_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.SPIOrg SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.GritProduct ADD CONSTRAINT
	FK_GritProduct_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.GritProduct SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.SSIOrg ADD CONSTRAINT
	FK_PAY_SSIOrg_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.SSIOrg SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE PAY.Contract ADD CONSTRAINT
	FK_PAY_Contract_OfficeDetail FOREIGN KEY
	(
	OfficeDetailRef
	) REFERENCES dbo.OfficeDetail
	(
	Serial
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE PAY.Contract SET (LOCK_ESCALATION = TABLE)
GO
COMMIT














If Not Exists(SELECT * FROM sys.objects WHERE name ='FactorReportTemplate')
Begin
	Create Table FactorReportTemplate(
		Serial Int Not Null,
		SelectedColumn Varchar(Max) Null,
		Detachment Varchar(Max) Null,
		Description Varchar(200) Null,
		OfficeDetailRef Int Null)
	Alter Table FactorReportTemplate Add Constraint PK_FactorReportTemplate Primary Key Clustered (Serial)
End
GO
