dstr_fd.sql

用友U8财务软件VB源程序, 本版本为2002年版本

if exists (select 1 from sysobjects where id = object_id('fd_accdef') and type = 'U') and COL_LENGTH('fd_accdef','cAccName') < 50
	begin
	drop index fd_accdef.cacc_name
	alter table fd_accdef alter column cAccName nvarchar(80) null
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_accdef') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_accdef'),'accdef_id','AllowsNull') = 0 and not exists (select 1 from  sysindexes where  id    = object_id('fd_accdef') and  name  = 'PK_FD_ACCDEF' and   indid > 0   and   indid < 255)
	alter table fd_accdef add constraint PK_FD_ACCDEF primary key ([accdef_id]) on 'default'
go


if exists (select 1 from sysobjects where id = object_id('fd_accdef') and type = 'U')
	begin
		if COLUMNPROPERTY( OBJECT_ID('fd_accdef'),'qcye_natural_mny','AllowsNull') is null
		alter table fd_accdef add qcye_natural_mny money null
		if COLUMNPROPERTY( OBJECT_ID('fd_accdef'),'qcjs_natural_mny','AllowsNull') is null
		alter table fd_accdef add qcjs_natural_mny money null
		if COLUMNPROPERTY( OBJECT_ID('fd_accdef'),'deficit_flag','AllowsNull') is null
		alter table fd_accdef add deficit_flag bit null
		if COLUMNPROPERTY( OBJECT_ID('fd_accdef'),'deficit_mny','AllowsNull') is null
		alter table fd_accdef add deficit_mny money null
		if COLUMNPROPERTY( OBJECT_ID('fd_accdef'),'deficitrestrict_flag','AllowsNull') is null
		alter table fd_accdef add deficitrestrict_flag bit null
		if COLUMNPROPERTY( OBJECT_ID('fd_accdef'),'digest','AllowsNull') is null
		alter table fd_accdef add digest fd_udt_digest null
		/*=====修改字段=====*/
		IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'FD_AccDef_mb_D' AND type = 'D')
		begin
		exec sp_unbindefault 'fd_accdef.Mb'
		drop default FD_AccDef_mb_D
		end
		IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'FD_AccDef_mh_D' AND type = 'D')
		begin
		exec sp_unbindefault 'fd_accdef.Mh'
		drop default FD_AccDef_mh_D
		end
		alter table fd_accdef alter column Mb money null
		alter table fd_accdef alter column Mh money null
		alter table fd_accdef alter column cAccBank nvarchar(100) null
		alter table fd_accdef alter column iDataSrc tinyint not null
		alter table fd_accdef alter column itype tinyint not null
		alter table fd_accdef alter column iio tinyint not null
		--alter table fd_accdef alter column Mb money null
		--alter table fd_accdef alter column Mh money null
	end
go


if not exists (select 1 from sysobjects where id = object_id('fd_accdef') and type = 'U')
	create table fd_accdef (
	accdef_id            fd_udt_id            not null,
	CAccID               nvarchar(50)         not null,
	CAccName             nvarchar(80)         not null,
	DopenDate            datetime             not null,
	accunit_id           fd_udt_id            not null,
	CUnitCode            nvarchar(50)         not null,
	CaccBank             nvarchar(100)        null,
	irate_id             fd_udt_id            not null,
	CIntrID              nvarchar(50)         not null,
	cad_id               fd_udt_id            null,
	CcadID               nvarchar(50)         null,
	Mb                   money                null,
	Mh                   money                null,
	qcye_natural_mny     money                null,
	qcjs_natural_mny     money                null,
	IDataSrc             tinyint              not null,
	Itype                tinyint              not null,
	Iio                  tinyint              not null,
	iYt                  bit                  null,
	yt_cad_id            fd_udt_id            null,
	cYtID                nvarchar(50)         null,
	Mqot                 money                null,
	cexch_name           nvarchar(8)          not null,
	deficit_flag         bit                  null,
	deficit_mny          money                null,
	deficitrestrict_flag bit                  null,
	Istate               bit                  null,
	Bdestroy             bit                  null,
	digest               fd_udt_digest        null,
	constraint PK_FD_ACCDEF primary key  (accdef_id)
	)
go


create unique  index ix_fd_accdef_code on fd_accdef (
CAccID
)
go


create   index ix_fd_accdef_cad on fd_accdef (
cad_id
)
go


create   index ix_fd_accdef_irate on fd_accdef (
irate_id
)
go


create   index ix_fd_accdef_unit on fd_accdef (
accunit_id
)
go


create   index ix_fd_accdef_datasrc on fd_accdef (
IDataSrc
)
go


if exists (select 1 from  sysindexes where  id = object_id('fd_accgrp') and name  = 'ix_fd_accgrp_id' and   indid > 0 and indid < 255)
   drop index fd_accgrp.ix_fd_accgrp_id
go


if not exists (select 1 from  sysobjects where  id = object_id('fd_accgrp') and type = 'U')
	create table fd_accgrp (
	accgrp_id            fd_udt_id            not null,
	accgrp_code          nvarchar(20)         not null,
	accgrp_name          nvarchar(50)         not null,
	parent_id            fd_udt_id            null,
	digest               fd_udt_digest        null,
	constraint PK_FD_ACCGRP primary key  (accgrp_id)
	)
go


create unique  index ix_fd_accgrp_id on fd_accgrp (
accgrp_id
)
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('fd_accgrplnk')
            and   name  = 'ix_fd_accgrplnk_acc'
            and   indid > 0
            and   indid < 255)
   drop index fd_accgrplnk.ix_fd_accgrplnk_acc
go


if not exists (select 1 from  sysobjects where  id = object_id('fd_accgrplnk') and   type = 'U')
	create table fd_accgrplnk (
	accgrp_id            fd_udt_id            not null,
	accdef_id            fd_udt_id            not null,
	constraint PK_FD_ACCGRPLNK primary key  (accgrp_id, accdef_id)
	)
go


create unique  index ix_fd_accgrplnk_acc on fd_accgrplnk (
accdef_id,
accgrp_id
)
go


if exists (select 1
            from  sysindexes
           where  id    = object_id('fd_accset')
            and   name  = 'ix_fd_accset_acc'
            and   indid > 0
            and   indid < 255)
   drop index fd_accset.ix_fd_accset_acc
go


if exists (select 1 from  sysobjects where  id = object_id('fd_accset') and type = 'U')
	begin
		if not COLUMNPROPERTY( OBJECT_ID('fd_accset'),'ufguid','AllowsNull') is null and exists (select 1 from  sysindexes   where  id    = object_id('fd_accset')   and   name  = 'fd_accset_PK' and  indid > 0  and   indid < 255)
		exec('alter table fd_accset drop fd_accset_PK')
		if not COLUMNPROPERTY( OBJECT_ID('fd_accset'),'ufguid','AllowsNull') is null
		exec('alter table fd_accset drop column ufguid')
	end
go


if exists (select 1 from  sysobjects where  id = object_id('fd_accset') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_accset'),'accset_id','AllowsNull') is null
	begin
		if COLUMNPROPERTY( OBJECT_ID('fd_accset'),'iid','AllowsNull') is null
		alter table fd_accset add iid int identity (1,1) not null
		IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'AddDateDfltaccdef_id' AND type = 'D')
		begin
		exec sp_unbindefault 'fd_accset.accdef_id'
		drop default AddDateDfltaccdef_id
		end
		alter table fd_accset add accdef_id fd_udt_id not null constraint AddDateDfltaccdef_id DEFAULT '000000000000000'
		alter table fd_accset drop constraint AddDateDfltaccdef_id
		IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'AddDateDfltaccset_id' AND type = 'D')
		begin
		exec sp_unbindefault 'fd_accset.accset_id'
		drop default AddDateDfltaccset_id
		end
		alter table fd_accset add accset_id fd_udt_id not null constraint AddDateDfltaccset_id DEFAULT '000000000000000'	
		alter table fd_accset drop constraint AddDateDfltaccset_id
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_accset') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_accset'),'accset_id','AllowsNull') = 0
	if exists (select 1 from fd_accset where accset_id = '000000000000000')
	begin
	exec ('update fd_accset set accset_id =910000000000000 + iid')
	exec ('alter table fd_accset drop column iid')
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_accset') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_accset'),'accdef_id','AllowsNull') = 0
	if exists (select 1 from fd_accset where accdef_id = '000000000000000')
	begin
	update fd_accset set accdef_id =fd_accdef.accdef_id from fd_accdef,fd_accset where fd_accset.CAccID=fd_accdef.CAccID
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_accset') and type = 'U') and COL_LENGTH('fd_accset','cAccId') < 50
	begin
	drop index fd_accset.cAccId
	alter table fd_accset alter column cAccId nvarchar(50) null
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_accset') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_accset'),'accset_id','AllowsNull') = 0 and not exists (select 1 from  sysindexes where  id    = object_id('fd_accset') and  name  = 'PK_FD_ACCSET' and   indid > 0   and   indid < 255)
	alter table fd_accset add constraint PK_FD_ACCSET primary key ([accset_id]) on 'default'
go


if exists (select 1 from  sysobjects where  id = object_id('fd_accset') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_accset'),'type_flag','AllowsNull') is null
	begin
	IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'AddDateDflttype_flag' AND type = 'D')
	begin
	exec sp_unbindefault 'fd_accset.type_flag'
	drop default AddDateDflttype_flag
	end
	alter table fd_accset add type_flag tinyint not null constraint AddDateDflttype_flag DEFAULT '0'
	alter table fd_accset drop constraint AddDateDflttype_flag
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_accset') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_accset'),'accset_id','AllowsNull') = 0 and not exists (select 1 from  sysindexes where  id    = object_id('fd_accset') and  name  = 'PK_FD_ACCSET' and   indid > 0   and   indid < 255)
	alter table fd_accset add constraint PK_FD_ACCSET primary key ([accset_id]) on 'default'
go


if exists (select 1 from  sysobjects where  id = object_id('fd_accset') and type = 'U')
	begin
	/*=====修改字段=====*/
	alter table fd_accset alter column CCode nvarchar(60) null
	alter table fd_accset alter column CPersonCode nvarchar(60) null
	alter table fd_accset alter column CDeptCode nvarchar(60) null
	alter table fd_accset alter column CCusCode nvarchar(60) null
	alter table fd_accset alter column CSupCode nvarchar(60) null
	alter table fd_accset alter column citem_id nvarchar(60) null
	end
go


if not exists (select 1 from  sysobjects where  id = object_id('fd_accset') and type = 'U')
	create table fd_accset (
	accset_id            fd_udt_id            not null,
	accdef_id            fd_udt_id            not null,
	CAccID               nvarchar(50)         null,
	type_flag            tinyint              not null,
	CCode                nvarchar(60)         null,
	CPersonCode          nvarchar(60)         null,
	CDeptCode            nvarchar(60)         null,
	CCusCode             nvarchar(60)         null,
	CSupCode             nvarchar(60)         null,
	citem_id             nvarchar(60)         null,
	citem_class          nvarchar(2)          null,
	MQc                  bit                  null,
	constraint PK_FD_ACCSET primary key  (accset_id)
	)
go


create   index ix_fd_accset_acc on fd_accset (
accdef_id,
type_flag
)
go


/*if exists (select 1 from  sysobjects where  id = object_id('fd_accsum') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_accsum'),'accdef_id','AllowsNull') is null
	begin
		IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'AddDateDfltfd_accsum' AND type = 'D')
		begin
		exec sp_unbindefault 'fd_accsum.accdef_id'
		drop default AddDateDfltfd_accsum
		end
		alter table fd_accsum add accdef_id fd_udt_id not null constraint AddDateDfltfd_accsum DEFAULT '000000000000000'
		alter table fd_accsum drop constraint AddDateDfltfd_accsum
	end
go*/


/*if exists (select 1 from sysobjects where id = object_id('fd_accsum') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_accsum'),'accdef_id','AllowsNull') = 0
	if exists (select 1 from fd_accsum where accdef_id = '000000000000000')
	begin
	update fd_accsum set accdef_id =fd_accdef.accdef_id from fd_accdef,fd_accsum where fd_accsum.CAccID=fd_accdef.CAccID
	end
go*/


if exists (select 1 from sysobjects where id = object_id('fd_accsum') and type = 'U') and COL_LENGTH('fd_accsum','cAccId') < 50
	begin
	alter table fd_accsum drop constraint aaaaaFd_AccSum_PK
	alter table fd_accsum alter column cAccId nvarchar(50) not null
	end
go


if exists (select 1 from sysobjects where id = object_id('fd_accsum') and type = 'U') and COLUMNPROPERTY( OBJECT_ID('fd_accsum'),'CAccID','AllowsNull') = 0 and not exists (select 1 from  sysindexes where  id    = object_id('fd_accsum') and  name  = 'PK_FD_ACCSUM' and   indid > 0   and   indid < 255)
	alter table fd_accsum add constraint PK_FD_ACCSUM primary key ([CAccID],[dbill_date]) on 'default'
go


if not exists (select 1 from  sysobjects where  id = object_id('fd_accsum') and type = 'U')