财务管理系统.sql

全面剖析财务管理的内容

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[分录表_凭证编号_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[分录表] DROP CONSTRAINT 分录表_凭证编号_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[分录表_科目代码_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[分录表] DROP CONSTRAINT 分录表_科目代码_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[本年分类帐簿_科目代码_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[本年分类帐簿] DROP CONSTRAINT 本年分类帐簿_科目代码_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[本年明细帐簿_科目代码_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[本年明细帐簿] DROP CONSTRAINT 本年明细帐簿_科目代码_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[本期分类帐簿_科目代码_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[本期分类帐簿] DROP CONSTRAINT 本期分类帐簿_科目代码_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[本期明细帐簿_科目代码_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[本期明细帐簿] DROP CONSTRAINT 本期明细帐簿_科目代码_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[分录表temp_科目代码_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[分录表temp] DROP CONSTRAINT 分录表temp_科目代码_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[分录表历史_科目代码_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[分录表历史] DROP CONSTRAINT 分录表历史_科目代码_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[科目余额表_科目代码_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[科目余额表] DROP CONSTRAINT 科目余额表_科目代码_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[历史分类帐簿_科目代码_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[历史分类帐簿] DROP CONSTRAINT 历史分类帐簿_科目代码_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[历史明细帐簿_科目代码_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[历史明细帐簿] DROP CONSTRAINT 历史明细帐簿_科目代码_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[项目科目对照表_科目代码_fk]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[项目科目对照表] DROP CONSTRAINT 项目科目对照表_科目代码_fk
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sf_pz凭证删除后]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sf_pz凭证删除后]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sf_pz凭证录入]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sf_pz凭证录入]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sf_pz凭证过帐]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sf_pz凭证过帐]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sf_pz调整凭证表]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sf_pz调整凭证表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[凭证表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[凭证表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[凭证表temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[凭证表temp]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[凭证表历史]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[凭证表历史]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[分录表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[分录表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[分录表temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[分录表temp]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[分录表历史]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[分录表历史]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[功能模块]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[功能模块]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[历史分类帐簿]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[历史分类帐簿]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[历史明细帐簿]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[历史明细帐簿]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[报表清单]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[报表清单]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[报表项目清单]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[报表项目清单]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[损益表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[损益表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[本年分类帐簿]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[本年分类帐簿]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[本年明细帐簿]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[本年明细帐簿]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[本期分类帐簿]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[本期分类帐簿]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[本期明细帐簿]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[本期明细帐簿]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[权限登记]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[权限登记]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[状态表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[状态表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[科目余额表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[科目余额表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[科目表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[科目表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[组别清单]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[组别清单]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[职工清单]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[职工清单]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[部门清单]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[部门清单]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[项目科目对照表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[项目科目对照表]
GO

CREATE TABLE [dbo].[凭证表] (
	[内部编号] [int] NULL ,
	[凭证状态] [char] (10) NULL ,
	[会计期间] [int] NULL ,
	[凭证字号] [char] (4) NULL ,
	[凭证编号] [int] NOT NULL ,
	[顺序号] [int] NULL ,
	[日期] [char] (10) NULL ,
	[附单据] [int] NULL ,
	[审核] [char] (20) NULL ,
	[过帐] [char] (20) NULL ,
	[制单] [char] (20) NULL ,
	[借方合计] [money] NULL ,
	[贷方合计] [money] NULL ,
	[过帐状态] [char] (4) NULL ,
	[顺序号码] [int] IDENTITY (1, 1) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[凭证表temp] (
	[内部编号] [int] NULL ,
	[凭证状态] [char] (10) NULL ,
	[会计期间] [int] NULL ,
	[凭证字号] [char] (4) NULL ,
	[凭证编号] [int] NULL ,
	[顺序号] [int] NULL ,
	[日期] [char] (10) NULL ,
	[附单据] [int] NULL ,
	[审核] [char] (20) NULL ,
	[过帐] [char] (20) NULL ,
	[制单] [char] (20) NULL ,
	[借方合计] [money] NULL ,
	[贷方合计] [money] NULL ,
	[顺序号码] [int] IDENTITY (1, 1) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[凭证表历史] (
	[内部编号] [int] NULL ,
	[凭证状态] [char] (10) NULL ,
	[会计期间] [int] NULL ,
	[凭证字号] [char] (4) NULL ,
	[凭证编号] [int] NULL ,
	[顺序号] [int] NULL ,
	[日期] [char] (10) NULL ,
	[附单据] [int] NULL ,
	[审核] [char] (20) NULL ,
	[过帐] [char] (20) NULL ,
	[制单] [char] (20) NULL ,
	[借方合计] [money] NULL ,
	[贷方合计] [money] NULL ,
	[过帐状态] [char] (4) NULL ,
	[顺序号码] [int] IDENTITY (1, 1) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[分录表] (
	[内部编号] [int] IDENTITY (1, 1) NOT NULL ,
	[分录编号] [int] NULL ,
	[会计期间] [int] NULL ,
	[凭证字号] [char] (4) NULL ,
	[凭证编号] [int] NOT NULL ,
	[摘要] [char] (40) NULL ,
	[科目代码] [char] (20) NULL ,
	[借方] [money] NULL ,
	[贷方] [money] NULL ,
	[数量] [int] NULL ,
	[单价] [money] NULL ,
	[结算方式] [char] (10) NULL ,
	[结算号] [int] NULL ,
	[结算日期] [char] (10) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[分录表temp] (
	[内部编号] [int] IDENTITY (1, 1) NOT NULL ,
	[分录编号] [int] NULL ,
	[会计期间] [int] NULL ,
	[凭证字号] [char] (4) NULL ,
	[凭证编号] [int] NULL ,
	[摘要] [char] (40) NULL ,
	[科目代码] [char] (20) NULL ,
	[借方] [money] NULL ,
	[贷方] [money] NULL ,
	[数量] [int] NULL ,
	[单价] [money] NULL ,
	[结算方式] [char] (10) NULL ,
	[结算号] [int] NULL ,
	[结算日期] [char] (10) NULL ,
	[附单据] [int] NULL ,
	[顺序号码] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[分录表历史] (
	[内部编号] [int] IDENTITY (1, 1) NOT NULL ,
	[分录编号] [int] NULL ,
	[会计期间] [int] NULL ,
	[凭证字号] [char] (4) NULL ,
	[凭证编号] [int] NULL ,
	[摘要] [char] (40) NULL ,
	[科目代码] [char] (20) NULL ,
	[借方] [money] NULL ,
	[贷方] [money] NULL ,
	[数量] [int] NULL ,
	[单价] [money] NULL ,
	[结算方式] [char] (10) NULL ,
	[结算号] [int] NULL ,
	[结算日期] [char] (10) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[功能模块] (
	[模块编号] [char] (50) NOT NULL ,
	[组别] [char] (20) NOT NULL ,
	[模块] [char] (20) NOT NULL ,
	[描述] [varchar] (100) NULL ,
	[模块类型] [int] NOT NULL ,
	[程序文件] [varchar] (100) NULL ,
	[菜单文件] [varchar] (100) NULL ,
	[打印文件] [varchar] (100) NULL ,
	[记帐参数] [varchar] (250) NULL ,
	[打印参数] [varchar] (250) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[历史分类帐簿] (
	[科目代码] [char] (20) NULL ,
	[期初余额] [char] (1) NULL ,
	[本期借方合计] [money] NULL ,
	[本期贷方合计] [money] NULL ,
	[本年借方累计] [money] NULL ,
	[本年贷方累计] [money] NULL ,
	[余额] [char] (1) NULL ,
	[摘要] [char] (40) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[历史明细帐簿] (
	[内部编号] [int] IDENTITY (1, 1) NOT NULL ,
	[分录编号] [int] NULL ,
	[会计期间] [int] NULL ,
	[凭证字号] [char] (4) NULL ,
	[凭证编号] [int] NULL ,
	[摘要] [char] (40) NULL ,
	[科目代码] [char] (20) NULL ,
	[借方] [money] NULL ,
	[贷方] [money] NULL ,
	[数量] [int] NULL ,
	[单价] [money] NULL ,
	[结算方式] [char] (10) NULL ,
	[结算号] [int] NULL ,
	[结算日期] [char] (10) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[报表清单] (
	[报表编号] [int] NULL ,
	[报表名称] [char] (40) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[报表项目清单] (
	[报表编号] [int] NULL ,
	[报表名称] [char] (40) NULL ,
	[项目代码] [int] NULL ,
	[项目名称] [char] (40) NULL ,
	[行号] [int] NULL ,
	[计算方法] [char] (10) NULL ,
	[内部编号] [int] IDENTITY (1, 1) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[损益表] (
	[项目] [char] (40) NULL ,
	[行号] [int] NULL ,
	[本月数] [money] NULL ,
	[本年累计数] [money] NULL ,
	[年份] [datetime] NULL ,
	[会计期间] [int] NULL ,
	[最后结算日期] [datetime] NULL ,
	[内部编号] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[本年分类帐簿] (
	[科目代码] [char] (20) NULL ,
	[期初余额] [char] (1) NULL ,
	[本期借方合计] [money] NULL ,
	[本期贷方合计] [money] NULL ,
	[本年借方累计] [money] NULL ,
	[本年贷方累计] [money] NULL ,
	[余额] [char] (1) NULL ,
	[摘要] [char] (40) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[本年明细帐簿] (
	[内部编号] [int] IDENTITY (1, 1) NOT NULL ,
	[分录编号] [int] NULL ,
	[会计期间] [int] NULL ,
	[凭证字号] [char] (4) NULL ,
	[凭证编号] [int] NULL ,
	[摘要] [char] (40) NULL ,
	[科目代码] [char] (20) NULL ,
	[借方] [money] NULL ,
	[贷方] [money] NULL ,
	[数量] [int] NULL ,
	[单价] [money] NULL ,
	[结算方式] [char] (10) NULL ,
	[结算号] [int] NULL ,
	[结算日期] [char] (10) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[本期分类帐簿] (
	[科目代码] [char] (20) NULL ,
	[期初余额] [char] (1) NULL ,
	[本期借方合计] [money] NULL ,
	[本期贷方合计] [money] NULL ,
	[本年借方累计] [money] NULL ,
	[本年贷方累计] [money] NULL ,
	[余额] [char] (1) NULL ,
	[摘要] [char] (40) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[本期明细帐簿] (
	[内部编号] [int] IDENTITY (1, 1) NOT NULL ,
	[分录编号] [int] NULL ,
	[会计期间] [int] NULL ,
	[凭证字号] [char] (4) NULL ,
	[凭证编号] [int] NULL ,
	[摘要] [char] (40) NULL ,
	[科目代码] [char] (20) NULL ,
	[借方] [money] NULL ,
	[贷方] [money] NULL ,
	[余额方向] [char] (4) NULL ,
	[余额] [money] NULL ,
	[数量] [int] NULL ,
	[单价] [money] NULL ,
	[结算方式] [char] (10) NULL ,
	[结算号] [int] NULL ,
	[结算日期] [char] (10) NULL ,
	[余额标记] [char] (4) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[权限登记] (
	[权限序号] [int] NOT NULL ,
	[编号] [char] (6) NOT NULL ,
	[编号类型] [int] NOT NULL ,
	[模块编号] [char] (10) NOT NULL ,
	[选中标记] [char] (1) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[状态表] (
	[内部编号] [int] IDENTITY (1, 1) NOT NULL ,
	[会计期间] [int] NULL ,
	[凭证字号] [char] (4) NULL ,
	[编号] [int] NULL ,
	[顺序号] [int] NULL ,
	[分录编号] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[科目余额表] (
	[科目代码] [char] (20) NULL ,
	[期初借方余额] [money] NULL ,
	[期初贷方余额] [money] NULL ,
	[本期借方发生额] [money] NULL ,
	[本期贷方发生额] [money] NULL ,
	[本年借方累计发生额] [money] NULL ,
	[本年贷方累计发生额] [money] NULL ,
	[本期借方余额] [money] NULL ,
	[本期贷方余额] [money] NULL ,
	[最后结算分录] [int] NULL ,
	[会计期间] [int] NULL ,
	[余额方向] [char] (4) NULL ,