Use ATNDBTest
Go

-- Delete All User Tables

If Exists(SELECT * FROM INFORMATION_SCHEMA.TABLES Where TABLE_SCHEMA = 'PAY' And TABLE_TYPE = 'BASE TABLE' And TABLE_NAME = 'MonthlyPaymentDetail') Drop Table PAY.MonthlyPaymentDetail 
If Exists(SELECT * FROM INFORMATION_SCHEMA.TABLES Where TABLE_SCHEMA = 'PAY' And TABLE_TYPE = 'BASE TABLE' And TABLE_NAME = 'MonthlyPaymentHeader') Drop Table PAY.MonthlyPaymentHeader 
If Exists(SELECT * FROM INFORMATION_SCHEMA.TABLES Where TABLE_SCHEMA = 'PAY' And TABLE_TYPE = 'BASE TABLE' And TABLE_NAME = 'PaymentComputingDetail') Drop Table PAY.PaymentComputingDetail 
If Exists(SELECT * FROM INFORMATION_SCHEMA.TABLES Where TABLE_SCHEMA = 'PAY' And TABLE_TYPE = 'BASE TABLE' And TABLE_NAME = 'PaymentComputingHeader') Drop Table PAY.PaymentComputingHeader 
If Exists(SELECT * FROM INFORMATION_SCHEMA.TABLES Where TABLE_SCHEMA = 'PAY' And TABLE_TYPE = 'BASE TABLE' And TABLE_NAME = 'PaymentComputingKind') Drop Table PAY.PaymentComputingKind 
If Exists(SELECT * FROM INFORMATION_SCHEMA.TABLES Where TABLE_SCHEMA = 'PAY' And TABLE_TYPE = 'BASE TABLE' And TABLE_NAME = 'MonthlyFactorPerEmployee') Drop Table PAY.MonthlyFactorPerEmployee 
If Exists(SELECT * FROM INFORMATION_SCHEMA.TABLES Where TABLE_SCHEMA = 'PAY' And TABLE_TYPE = 'BASE TABLE' And TABLE_NAME = 'MonthIndex') Drop Table PAY.MonthIndex 
/*=============================================================================================================*/
If Not Exists(SELECT * FROM sys.objects Where NAME = 'PK_PAY_Employee') 
		Alter Table PAY.Employee Add Constraint PK_PAY_Employee Primary Key Clustered (Serial)
GO
/*=============================================================================================================*/
If Not Exists(SELECT * FROM sys.objects Where NAME = 'PK_PAY_Factor') 
		Alter Table PAY.Factor Add Constraint PK_PAY_Factor Primary Key Clustered (Serial)
GO
/*=============================================================================================================*/
Create Table PAY.MonthIndex(
	Serial Int Not Null, -----
	FADes Varchar(50) Null, --
	ENDes Varchar(50) Null) --
Go
Alter Table PAY.MonthIndex Add Constraint PK_MonthIndex Primary Key Clustered (Serial)
Go
/*=============================================================================================================*/
Create Table PAY.MonthlyFactorPerEmployee(
	[Serial] Bigint Identity(1,1) Not Null, 
	[Category] Smallint Not Null, ----------
	[EmployeeRef] Bigint Not Null, ---------
	[FactorRef] Bigint Not Null, -----------
	[Value] Varchar(15) Null, --------------
	[Month] Int Null, ---------------- 
	[OfficePeriodRef] Int Null, ------------
	[OfficeDetailRef] Int Null) -----------
Go
Alter Table PAY.MonthlyFactorPerEmployee Add Constraint PK_PAY_MonthlyFactorPerEmployee Primary Key Clustered (Serial)
Go
Alter Table PAY.MonthlyFactorPerEmployee Add Constraint FK_PAY_MonthlyFactorPerEmployee_Employee Foreign Key(EmployeeRef) References PAY.Employee (Serial)
GO
Alter Table PAY.MonthlyFactorPerEmployee Add Constraint FK_PAY_MonthlyFactorPerEmployee_Factor Foreign Key(FactorRef) References PAY.Factor (Serial)
GO
Alter Table PAY.MonthlyFactorPerEmployee Add Constraint FK_PAY_MonthlyFactorPerEmployee_MonthIndex Foreign Key(Month) References PAY.MonthIndex (Serial)
Go
Alter Table PAY.MonthlyFactorPerEmployee Add Constraint FK_PAY_MonthlyFactorPerEmployee_OfficeDetail Foreign Key(OfficeDetailRef) References dbo.OfficeDetail (Serial)
GO
Alter Table PAY.MonthlyFactorPerEmployee Add Constraint FK_PAY_MonthlyFactorPerEmployee_OfficePeriod Foreign Key(OfficePeriodRef) References dbo.OfficePeriod (Serial)
GO
/*=============================================================================================================*/
Create Table PAY.PaymentComputingKind(
	Serial Smallint Not Null, --
	FADes Varchar(50) Null, ----
	ENDes Varchar(50) Null) ----
Go
Alter Table PAY.PaymentComputingKind Add Constraint PK_PaymentComputingKind Primary Key Clustered (Serial)
Go
/*=============================================================================================================*/
Create Table PAY.PaymentComputingHeader(
	Serial                  Bigint Identity(1,1) Not Null, --
	Code				    Bigint NULL, --------------------
	PaymentComputingKindRef	Smallint Not Null, --------------
	AccDocumentHeaderRef    Bigint NULL, --------------------
	[Year]		            Int NULL, -----------------------
	MonthIndexRef           Int NULL, -----------------------
	DaysOfMonth			    Int Null, -----------------------
	EffectiveDate           Char(10) Null, ------------------
	NumberOfEmployee        Bigint Null, --------------------
	Description             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, -----------------------
	OfficePeriodRef         Int Null, -----------------------
 	RowVersion              DateTime)
Go
Alter Table PAY.PaymentComputingHeader Add Constraint PK_PAY_PaymentComputingHeader Primary Key Clustered (Serial)
Go
Alter Table PAY.PaymentComputingHeader Add Constraint FK_PAY_PaymentComputingHeader_AccDocumentHeader Foreign Key(AccDocumentHeaderRef) References dbo.AccDocumentHeader (Serial)
Go
Alter Table PAY.PaymentComputingHeader Add Constraint FK_PAY_PaymentComputingHeader_MonthIndex Foreign Key(MonthIndexRef) References PAY.MonthIndex (Serial)
Go
Alter Table PAY.PaymentComputingHeader Add Constraint FK_PAY_PaymentComputingHeader_PaymentComputingKind Foreign Key(PaymentComputingKindRef) References PAY.PaymentComputingKind (Serial)
Go
Alter Table PAY.PaymentComputingHeader Add Constraint FK_PAY_PaymentComputingHeader_Recorder Foreign Key(RecorderRef) References dbo.Users (Serial)
Go
Alter Table PAY.PaymentComputingHeader Add Constraint FK_PAY_PaymentComputingHeader_Verifier Foreign Key(VerifierRef) References dbo.Users (Serial)
Go
Alter Table PAY.PaymentComputingHeader Add Constraint FK_PAY_PaymentComputingHeader_OfficeDetail Foreign Key(OfficeDetailRef) References dbo.OfficeDetail (Serial)
Go
Alter Table PAY.PaymentComputingHeader Add Constraint FK_PAY_PaymentComputingHeader_OfficePeriod Foreign Key(OfficePeriodRef) References dbo.OfficePeriod (Serial)
GO
/*=============================================================================================================*/
Create Table PAY.PaymentComputingDetail(
	Serial                         Bigint Identity(1,1) Not Null, --
	PaymentComputingHeaderRef        Bigint NOT NULL, --------------
	ComputingCode                  Int NOT NULL, -------------------
	ComputingName                  Varchar(50) NOT NULL, -----------
	EmployeeRef                    Bigint NULL, --------------------
	Periority                      Int 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) ------------
Go
Alter Table PAY.PaymentComputingDetail Add Constraint PK_PAY_PaymentComputingDetail Primary Key Clustered (Serial)
Go
Alter Table PAY.PaymentComputingDetail Add Constraint FK_PAY_PaymentComputingDetail_PaymentComputingHeader Foreign Key(PaymentComputingHeaderRef) References PAY.PaymentComputingHeader (Serial)
Go
Alter Table PAY.PaymentComputingDetail Add Constraint FK_PAY_PaymentComputingDetail_Employee Foreign Key(EmployeeRef) References PAY.Employee (Serial)
Go
Alter Table PAY.PaymentComputingDetail Add Constraint FK_PAY_PaymentComputingDetail_AccAdjutantL1CodeBook_Credit Foreign Key(CreditAccAdjutantL1CodeBookRef) References dbo.AccAdjutantL1CodeBook (Serial)
Go
Alter Table PAY.PaymentComputingDetail Add Constraint FK_PAY_PaymentComputingDetail_AccAdjutantL1CodeBook_Debit Foreign Key(DebitAccAdjutantL1CodeBookRef) References dbo.AccAdjutantL1CodeBook (Serial)
Go
Alter Table PAY.PaymentComputingDetail Add Constraint FK_PAY_PaymentComputingDetail_AccEntityL1CodeBook_Credit Foreign Key(CreditAccEntityL1CodeBookRef) References dbo.AccEntityL1CodeBook (Serial)
Go
Alter Table PAY.PaymentComputingDetail Add Constraint FK_PAY_PaymentComputingDetail_AccEntityL1CodeBook_Debit Foreign Key(DebitAccEntityL1CodeBookRef) References dbo.AccEntityL1CodeBook (Serial)
Go
Alter Table PAY.PaymentComputingDetail Add Constraint FK_PAY_PaymentComputingDetail_AccEntityL2CodeBook_Credit Foreign Key(CreditAccEntityL2CodeBookRef) References dbo.AccEntityL2CodeBook (Serial)
Go
Alter Table PAY.PaymentComputingDetail Add Constraint FK_PAY_PaymentComputingDetail_AccEntityL2CodeBook_Debit Foreign Key(DebitAccEntityL2CodeBookRef) References dbo.AccEntityL2CodeBook (Serial)
Go
