财务管理.sql

制造业ERP系统搜集

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].[帐簿初始化表_科目代码_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].[GetField]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetField]
GO

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sf_初始化帐户]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sf_初始化帐户]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sf_启用帐户]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sf_启用帐户]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sf_期末结帐]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sf_期末结帐]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sf_计算资产负债表]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sf_计算资产负债表]
GO

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t2]
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].[t1] (
	[编号] [int] NOT NULL ,
	[凭证编号] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[摘要] [char] (40) COLLATE Chinese_PRC_CI_AS NULL ,
	[科目代码] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[借方] [money] NULL ,
	[贷方] [money] NULL ,
	[数量] [int] NULL ,
	[单价] [money] NULL ,
	[结算方式] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[结算号] [int] NULL ,
	[结算日期] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[t2] (
	[凭证编号] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[会计期间] [int] NULL ,
	[凭证字号] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[日期] [datetime] NULL ,
	[附单据] [int] NULL ,
	[制单] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[借方合计] [money] NULL ,
	[贷方合计] [money] NULL ,
	[过账状态] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
	[凭证状态] [char] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[凭证表] (
	[凭证编号] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[会计期间] [int] NULL ,
	[凭证字号] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[日期] [datetime] NULL ,
	[附单据] [int] NULL ,
	[制单] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[借方合计] [money] NULL ,
	[贷方合计] [money] NULL ,
	[过账状态] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
	[凭证状态] [char] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[凭证表历史] (
	[凭证编号] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[会计期间] [int] NULL ,
	[凭证字号] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[日期] [datetime] NULL ,
	[附单据] [int] NULL ,
	[制单] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[借方合计] [money] NULL ,
	[贷方合计] [money] NULL ,
	[过账状态] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
	[凭证状态] [char] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

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

CREATE TABLE [dbo].[分录表历史] (
	[编号] [int] NOT NULL ,
	[凭证编号] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[摘要] [char] (40) COLLATE Chinese_PRC_CI_AS NULL ,
	[科目代码] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[借方] [money] NULL ,
	[贷方] [money] NULL ,
	[数量] [int] NULL ,
	[单价] [money] NULL ,
	[结算方式] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[结算号] [int] NULL ,
	[结算日期] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[帐簿初始化表] (
	[科目代码] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[科目名称] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[累计借方] [money] NULL ,
	[累计贷方] [money] NULL ,
	[期初余额] [money] NULL ,
	[余额方向] [char] (4) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

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

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

CREATE TABLE [dbo].[本期汇总账簿] (
	[科目代码] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[会计期间] [int] NULL ,
	[期初余额] [money] NULL ,
	[本期借方合计] [money] NULL ,
	[本期贷方合计] [money] NULL ,
	[本年借方累计] [money] NULL ,
	[本年贷方累计] [money] NULL ,
	[余额] [money] NULL ,
	[余额方向] [char] (4) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[科目余额表] (
	[科目代码] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[期初借方余额] [money] NULL ,
	[期初贷方余额] [money] NULL ,
	[本期借方发生额] [money] NULL ,
	[本期贷方发生额] [money] NULL ,
	[本年借方累计发生额] [money] NULL ,
	[本年贷方累计发生额] [money] NULL ,
	[本期借方余额] [money] NULL ,
	[本期贷方余额] [money] NULL ,
	[最后结算分录] [int] NULL ,
	[会计期间] [int] NULL ,
	[余额方向] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
	[内部编号] [int] IDENTITY (1, 1) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[科目表] (
	[科目代码] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[科目名称] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[助记码] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[科目类别] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[是否存货科目] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[余额方向] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
	[数量单位] [char] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[系统参数表] (
	[编号] [int] IDENTITY (1, 1) NOT NULL ,
	[参数名称] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[取值] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[资产负债表] (
	[会计期间] [int] NOT NULL ,
	[现金及现金等价物] [money] NULL ,
	[应收账款] [money] NULL ,
	[坏账准备] [money] NULL ,
	[应收账款净值] [money] NULL ,
	[流动资产总计] [money] NULL ,
	[固定资产原值] [money] NULL ,
	[累计折旧] [money] NULL ,
	[固定资产总计] [money] NULL ,
	[其他资产] [money] NULL ,
	[资产总计] [money] NULL ,
	[应付账款] [money] NULL ,
	[预收账款] [money] NULL ,
	[应付工资] [money] NULL ,
	[其他负债] [money] NULL ,
	[负债总计] [money] NULL ,
	[实收资本] [money] NULL ,
	[资本公积] [money] NULL ,
	[赢余公积] [money] NULL ,
	[未分配利润] [money] NULL ,
	[所有者权益总计] [money] NULL ,
	[负债及所有者权益总计] [money] NULL 
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure GetField @tablename char(30), @alias varchar(10) = ''
as
	declare @list varchar(500), @list1 varchar(500)         -- 长度不够时用list1
	select @list = ''
	select @list1=''
	declare @id int
	declare @fieldname varchar(30)
	select @id = id from sysobjects where name = @tablename
	declare mycur cursor for select name from syscolumns where id = @id
	open mycur
	fetch next from mycur into @fieldname
	WHILE (@@FETCH_STATUS = 0 )
	BEGIN
		if @alias <> ''
		begin