EXEC sp_addtype N'd_AccType', N'nvarchar (2)'
GO
EXEC sp_addtype N'd_Boolean', N'char (1)'
GO
EXEC sp_addtype N'd_AccNo', N'nvarchar (12)'
GO
EXEC sp_addtype N'd_AccDescription', N'nvarchar (80)'
GO
EXEC sp_addtype N'd_SpecialAccType', N'varchar (3)'
GO
EXEC sp_addtype N'd_CurrencyCode', N'nvarchar (5)'
GO
EXEC sp_addtype N'd_Project', N'nvarchar (20)'
GO
EXEC sp_addtype N'd_Dept', N'nvarchar (20)'
GO
EXEC sp_addtype N'd_BudgetName', N'nvarchar (20)'
GO
EXEC sp_addtype N'd_PeriodNo', N'int'
GO
EXEC sp_addtype N'd_Money', N'decimal(19,2)'
GO
EXEC sp_addtype N'd_JournalType', N'nvarchar (10)'
GO
EXEC sp_addtype N'd_SourceType', N'varchar (2)'
GO
EXEC sp_addtype N'd_GLDescription', N'nvarchar (100)'
GO
EXEC sp_addtype N'd_CurrencyRate', N'decimal(19, 12)'
GO
CREATE TABLE [dbo].[ConsolidateProfile](
[AccountBookKey] [bigint] NOT NULL,
[CompanyName] [nvarchar](80) NULL,
[Remark] [nvarchar](12) NULL,
[DisplayName] [nvarchar](40) NULL,
[UseLiveDB] [d_Boolean] NOT NULL,
[LiveDBServerName] [nvarchar] (100) NULL,
[LiveDBUserName] [nvarchar] (20) NULL,
[LiveDBPassword] [nvarchar] (100) NULL,
[LiveDBDatabaseName] [nvarchar] (20) NULL,
[FiscalYearStartPeriod] [int] NULL,
[ActualDataStartPeriod] [int] NULL,
[CurrencyDecimal] [int] NOT NULL,
[UseLiveStockBalance] [d_Boolean] NOT NULL,
[UseBankerRounding] [d_Boolean] NOT NULL,
[OpeningBalanceInYTD] [d_Boolean] NOT NULL,
[BalanceStockAccountNo] [d_AccNo] NULL,
[LastUpdatedData] [datetime] NULL,
CONSTRAINT [PK_ConsolidateProfile] PRIMARY KEY NONCLUSTERED
(
[AccountBookKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AccGroup] (
[AccountBookKey] [bigint] NOT NULL,
[AccType] [d_AccType] NOT NULL,
[Seq] [int] NOT NULL,
[NormalBalance] char(1) NOT NULL,
[Group] char(2) NOT NULL,
[Level] [int] NOT NULL,
CONSTRAINT [PK_AccGroup] PRIMARY KEY NONCLUSTERED
(
[AccountBookKey] ASC,
[AccType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AccType] (
[AccountBookKey] [bigint] NOT NULL,
[AccType] [d_AccType] NOT NULL,
[Description] [nvarchar] (40) NULL ,
[Desc2] [nvarchar] (40) NULL ,
[IsBSType] [d_Boolean] NOT NULL ,
[IsSystemType] [d_Boolean] NOT NULL ,
CONSTRAINT [PK_AccType] PRIMARY KEY NONCLUSTERED
(
[AccountBookKey] ASC,
[AccType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[GLMast](
[AccountBookKey] [bigint] NOT NULL,
[AccNo] [dbo].[d_AccNo] NOT NULL,
[ParentAccNo] [dbo].[d_AccNo] NULL,
[Description] [dbo].[d_AccDescription] NULL,
[Desc2] [dbo].[d_AccDescription] NULL,
[AccType] [dbo].[d_AccType] NOT NULL,
[SpecialAccType] [dbo].[d_SpecialAccType] NULL,
[CurrencyCode] [dbo].[d_CurrencyCode] NOT NULL,
[CashFlowCategory] [char](1) NULL,
CONSTRAINT [PK_GLMast] PRIMARY KEY NONCLUSTERED
(
[AccountBookKey] ASC,
[AccNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AssetLink](
[AccountBookKey] [bigint] NOT NULL,
[AssetAccNo] [dbo].[d_AccNo] NOT NULL,
[AssetDeprnAccNo] [dbo].[d_AccNo] NOT NULL,
CONSTRAINT [PK_AssetLink] PRIMARY KEY NONCLUSTERED
(
[AccountBookKey] ASC,
[AssetAccNo] ASC,
[AssetDeprnAccNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Project](
[AccountBookKey] [bigint] NOT NULL,
[ProjNo] [dbo].[d_Project] NOT NULL,
[ParentProjNo] [dbo].[d_Project] NULL,
[Description] [nvarchar](40) NULL,
[Desc2] [nvarchar](40) NULL,
[IsActive] [dbo].[d_Boolean] NOT NULL,
[LastUpdate] [int] NOT NULL,
CONSTRAINT [PK_Project] PRIMARY KEY NONCLUSTERED
(
[AccountBookKey] ASC,
[ProjNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Dept](
[AccountBookKey] [bigint] NOT NULL,
[DeptNo] [dbo].[d_Dept] NOT NULL,
[ParentDeptNo] [dbo].[d_Dept] NULL,
[Description] [nvarchar](40) NULL,
[Desc2] [nvarchar](40) NULL,
[IsActive] [dbo].[d_Boolean] NOT NULL,
[LastUpdate] [int] NOT NULL,
CONSTRAINT [PK_Dept] PRIMARY KEY NONCLUSTERED
(
[AccountBookKey] ASC,
[DeptNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Budget](
[AccountBookKey] [bigint] NOT NULL,
[BudgetKey] [int] NOT NULL,
[BudgetName] [dbo].[d_BudgetName] NOT NULL,
CONSTRAINT [PK_Budget] PRIMARY KEY NONCLUSTERED
(
[AccountBookKey] ASC,
[BudgetKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BudgetPBalance](
[AccountBookKey] [bigint] NOT NULL,
[BudgetKey] [int] NOT NULL,
[PeriodNo] [dbo].[d_PeriodNo] NOT NULL,
[AccNO] [dbo].[d_AccNo] NOT NULL,
[ProjNo] [dbo].[d_Project] NULL,
[DeptNo] [dbo].[d_Dept] NULL,
[Amount] [dbo].[d_Money] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_BudgetPBalance] ON [dbo].[BudgetPBalance]
(
[AccountBookKey] ASC,
[BudgetKey] ASC,
[PeriodNo] ASC,
[AccNO] ASC,
[ProjNo] ASC,
[DeptNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE [dbo].[StockSet](
[AccountBookKey] [bigint] NOT NULL,
[StockSetKey] [int] NOT NULL,
[OpenStock] [dbo].[d_AccNo] NOT NULL,
[CloseStock] [dbo].[d_AccNo] NOT NULL,
[BalanceStock] [dbo].[d_AccNo] NOT NULL,
CONSTRAINT [PK_StockSet] PRIMARY KEY NONCLUSTERED
(
[AccountBookKey] ASC,
[StockSetKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[StockPBalance](
[AccountBookKey] [bigint] NOT NULL,
[StockSetKey] [int] NOT NULL,
[PeriodNo] [dbo].[d_PeriodNo] NOT NULL,
[ProjNo] [dbo].[d_Project] NULL,
[DeptNo] [dbo].[d_Dept] NULL,
[Balance] [dbo].[d_Money] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_StockPBalance] ON [dbo].[StockPBalance]
(
[AccountBookKey] ASC,
[StockSetKey] ASC,
[PeriodNo] ASC,
[ProjNo] ASC,
[DeptNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PBalance](
[AccountBookKey] [bigint] NOT NULL,
[PeriodNo] [dbo].[d_PeriodNo] NOT NULL,
[AccNo] [dbo].[d_AccNo] NOT NULL,
[ProjNo] [dbo].[d_Project] NULL,
[DeptNo] [dbo].[d_Dept] NULL,
[DR] [dbo].[d_Money] NULL,
[CR] [dbo].[d_Money] NULL,
[HomeDR] [dbo].[d_Money] NULL,
[HomeCR] [dbo].[d_Money] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PBalance] ON [dbo].[PBalance]
(
[AccountBookKey] ASC,
[PeriodNo] ASC,
[AccNo] ASC,
[ProjNo] ASC,
[DeptNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OBalance](
[AccountBookKey] [bigint] NOT NULL,
[PeriodNo] [dbo].[d_PeriodNo] NOT NULL,
[AccNo] [dbo].[d_AccNo] NOT NULL,
[ProjNo] [dbo].[d_Project] NULL,
[DeptNo] [dbo].[d_Dept] NULL,
[DR] [dbo].[d_Money] NULL,
[CR] [dbo].[d_Money] NULL,
[HomeDR] [dbo].[d_Money] NULL,
[HomeCR] [dbo].[d_Money] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_OBalance] ON [dbo].[OBalance]
(
[AccountBookKey] ASC,
[PeriodNo] ASC,
[AccNo] ASC,
[ProjNo] ASC,
[DeptNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE [dbo].[GLDTL](
[AccountBookKey] [bigint] NOT NULL,
[GLDtlKey] [bigint] NOT NULL,
[AccNo] [dbo].[d_AccNo] NOT NULL,
[DEAccNo] [dbo].[d_AccNo] NULL,
[ProjNo] [dbo].[d_Project] NULL,
[DeptNo] [dbo].[d_Dept] NULL,
[CurrencyCode] [dbo].[d_CurrencyCode] NOT NULL,
[CurrencyRate] [dbo].[d_CurrencyRate] NOT NULL,
[ToHomeRate] [dbo].[d_CurrencyRate] NOT NULL,
[OrgDR] [dbo].[d_Money] NULL,
[OrgCR] [dbo].[d_Money] NULL,
[DR] [dbo].[d_Money] NULL,
[CR] [dbo].[d_Money] NULL,
[HomeDR] [dbo].[d_Money] NULL,
[HomeCR] [dbo].[d_Money] NULL,
[TransDate] [datetime] NOT NULL,
[Description] [dbo].[d_GLDescription] NULL,
[RefNo1] [nvarchar](20) NULL,
[RefNo2] [nvarchar](20) NULL,
[DEKey] [bigint] NULL,
CONSTRAINT [PK_GLDTL] PRIMARY KEY NONCLUSTERED
(
[AccountBookKey] ASC,
[GLDtlKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[FiscalYear](
[AccountBookKey] [bigint] NOT NULL,
[FiscalYearName] [nvarchar](20) NOT NULL,
[FromDate] [datetime] NOT NULL,
[ToDate] [datetime] NOT NULL,
[IsActive] [dbo].[d_Boolean] NOT NULL,
CONSTRAINT [PK_FiscalYear] PRIMARY KEY NONCLUSTERED
(
[AccountBookKey] ASC,
[FiscalYearName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
