📄 dstr_fd.sql
字号:
settle_b_id
)
go
if exists (select 1
from sysindexes
where id = object_id('fd_transactions')
and name = 'ix_fd_transactions'
and indid > 0
and indid < 255)
drop index fd_transactions.ix_fd_transactions
go
if not exists (select 1 from sysobjects where id = object_id('fd_transactions') and type = 'U')
create table fd_transactions (
iid decimal(13) identity,
transactions_id fd_udt_id not null,
transactions_code nchar(10) not null,
bill_date datetime not null,
rcv_acc_id fd_udt_id null,
pay_acc_id fd_udt_id null,
fixed_acc_id fd_udt_id null,
minus_acc_id fd_udt_id null,
sum_mny money not null,
mcde_mny money null,
mh_mny money null,
mcdeh_mny money null,
commission_mny money null,
sum_tax money null,
exchange_rate float null,
correspond_vch_id fd_udt_id null,
irate_id fd_udt_id null,
cad_id fd_udt_id null,
yt_cad_id fd_udt_id null,
fixedmonth_num smallint null,
from_date datetime null,
to_date datetime null,
return_date datetime null,
vouchertype_flag tinyint null,
calctype_flag tinyint null,
settle_code varchar(3) null,
settle_flag bit null default 0,
loan_flag tinyint null default 0,
importexport_flag tinyint null default 0,
success_flag bit null default 0,
natural_mny money null,
money_name nvarchar(8) null,
pz_code varchar(8) null,
invoice_code varchar(50) null,
i_cheque_code varchar(50) null,
o_cheque_code varchar(50) null,
bill_name fd_udt_username null,
check_name fd_udt_username null,
book_name fd_udt_username null,
equalsubject_code nvarchar(20) null,
digest fd_udt_digest null,
source nvarchar(255) null,
purpose nvarchar(255) null,
protest nvarchar(255) null,
userdefine1 varchar(255) null,
userdefine2 varchar(255) null,
userdefine3 varchar(255) null,
userdefine4 varchar(255) null,
userdefine5 varchar(255) null,
userdefine6 varchar(255) null,
userdefine7 varchar(255) null,
userdefine8 varchar(255) null,
userdefine9 varchar(255) null,
userdefine10 varchar(255) null,
userdefine11 varchar(255) null,
userdefine12 varchar(255) null,
userdefine13 varchar(255) null,
userdefine14 varchar(255) null,
userdefine15 varchar(255) null,
userdefine16 varchar(255) null,
userdefine17 ntext null,
userdefine18 ntext null,
userdefine19 ntext null,
userdefine20 ntext null,
userdefine21 numeric null,
userdefine22 numeric null,
userdefine23 numeric null,
userdefine24 numeric null,
userdefine25 numeric null,
userdefine26 numeric null,
userdefine27 datetime null,
userdefine28 datetime null,
userdefine29 datetime null,
userdefine30 datetime null,
constraint PK_FD_TRANSACTIONS primary key (iid)
)
go
create index ix_fd_transactions on fd_transactions (
transactions_id,
transactions_code
)
go
if exists (select 1 from sysobjects where id = object_id('fd_vouch') and type = 'U') and COL_LENGTH('fd_vouch','cBus_id') < 12
begin
alter table fd_vouch alter column cBus_id varchar(12) not null
alter table fd_vouch alter column cAcc1_id nvarchar(50) not null
alter table fd_vouch alter column cAcc2_id nvarchar(50) null
end
go
if not exists (select 1 from sysobjects where id = object_id('fd_budgetdata') and type = 'U')
create table fd_budgetdata (
iid int not null,
ilineindex int not null,
iflid int not null,
islid int null,
mdeclare money null,
scurcode int not null default 0,
mapprove money null,
dpay datetime null,
sremark nvarchar(500) null,
sbank nvarchar(80) null,
constraint PK_FD_BUDGETDATA primary key (iid,ilineindex)
)
go
if not exists (select 1 from sysobjects where id = object_id('FD_ReportTemp') and type = 'U')
create table FD_ReportTemp (
UserID varchar(255) NOT NULL,
ReportID varchar(255) NOT NULL,
LineID int NOT NULL,
Field01 varchar(255) NULL,
Field02 varchar(255) NULL,
Field03 varchar(255) NULL,
Field04 varchar(255) NULL,
Field05 varchar(255) NULL,
Field06 varchar(255) NULL,
Field07 varchar(255) NULL,
Field08 varchar(255) NULL,
Field09 varchar(255) NULL,
Field10 varchar(255) NULL,
Field11 varchar(255) NULL,
Field12 varchar(255) NULL,
Field13 varchar(255) NULL,
Field14 varchar(255) NULL,
Field15 varchar(255) NULL,
Field16 varchar(255) NULL,
Field17 varchar(255) NULL,
Field18 varchar(255) NULL,
Field19 varchar(255) NULL,
Field20 varchar(255) NULL
)
go
if not exists (select 1 from sysobjects where id = object_id('fd_budgethead') and type = 'U')
create table fd_budgethead (
iid int identity,
sname nvarchar(50) not null,
accunit_id fd_udt_id not null,
ddeclare datetime not null,
dapprove datetime null,
dstart datetime not null,
dend datetime not null,
sbill nvarchar(20) not null,
sconfirm nvarchar(20) null,
scheck nvarchar(20) null,
btype bit null default 0,
ibudgetid int null,
constraint PK_FD_BUDGETHEAD primary key (iid)
)
go
if not exists (select 1 from sysobjects where id = object_id('fd_projdef') and type = 'U')
create table fd_projdef (
iid int identity,
sprjcode varchar(2) not null,
sprjname nvarchar(80) null default '－1',
iparentid int not null default -1,
bprjclass bit null default 0,
smemo nvarchar(500) null,
sprjaddon nvarchar(2000) null,
constraint PK_FD_PROJDEF primary key (iid)
)
go
if not exists (select 1 from sysobjects where id = object_id('FD_borQuaLimSet') and type = 'U')
CREATE TABLE FD_borQuaLimSet (
cUnitCode varchar(20) NOT NULL,
avalDateStart datetime NOT NULL,
avalDateEnd datetime NOT NULL,
btype bit NOT NULL,
borLimValue numeric(18, 2) NOT NULL,
memo varchar(120) NULL,
constraint pk_FD_borQuaLimSet primary key (cUnitCode,avalDateStart)
)
GO
if not exists (select 1 from sysobjects where id = object_id('FD_creClass') and type = 'U')
CREATE TABLE FD_creClass (
creClass varchar(10) NOT NULL,
lowMark numeric(18, 2) NOT NULL,
highMark numeric(18, 2) NOT NULL,
borLim numeric(18, 2) NOT NULL,
constraint PK_FD_creClass primary key (creClass)
)
GO
if not exists (select 1 from sysobjects where id = object_id('FD_creEstamate') and type = 'U')
CREATE TABLE FD_creEstamate (
cUnitCode varchar(20) NOT NULL,
estDate datetime NULL,
perSTART datetime NULL,
perEnd datetime NULL,
creClass varchar(10) NULL,
itemID int NOT NULL,
realValue varchar(18) NULL,
realMark numeric(18, 2) NULL,
Memo varchar(120) NULL,
constraint PK_FD_creEstamate primary key (cUnitCode,itemID)
)
GO
if not exists (select 1 from sysobjects where id = object_id('FD_creEvaPara') and type = 'U')
CREATE TABLE FD_creEvaPara (
itemID int IDENTITY(1, 1) NOT NULL,
itemName varchar(50) NOT NULL,
itemType bit NOT NULL DEFAULT 1,
selFlag bit NULL DEFAULT 0,
calFormu varchar(180) NULL,
stanValue numeric(18, 2) NULL DEFAULT 0,
stanMark numeric(18, 2) NULL DEFAULT 0,
calMarkFormu varchar(180) NULL,
calMarkFormu1 varchar(180) NULL,
Standard varchar(12) NULL,
quaMark int NULL DEFAULT 0,
memo varchar(120) NULL,
constraint PK_FD_creEvaPara primary key (itemID)
)
GO
if not exists (select 1 from sysobjects where id = object_id('FD_tmpValue') and type = 'U')
CREATE TABLE FD_tmpValue (
realValue numeric(18, 2) NOT NULL DEFAULT 0,
stanValue numeric(18, 2) NOT NULL DEFAULT 0,
stanMark numeric(18, 2) NOT NULL DEFAULT 0,
constraint PK_FD_tmpValue primary key (realValue,stanValue,stanMark)
)
GO
if exists (select 1
from sysobjects
where name = 'fd_copyfields'
and type = 'P')
drop procedure fd_copyfields
go
if exists (select 1
from sysobjects
where name = 'fd_deletefields'
and type = 'P')
drop procedure fd_deletefields
go
if not exists (select 1 from sysobjects where id = object_id('fd_accUpgrade') and type = 'U')
create table fd_accUpgrade (
accdef_id character(15) not null,
old_Caccid varchar(40) not null,
new_Caccid varchar(40) not null,
upgrade_date datetime not null,
constraint PK_FD_ACCUPGRADE primary key (accdef_id, old_Caccid, new_Caccid, upgrade_date)
)
go
/*单据类型定义,增加*/
create procedure dbo.fd_copyfields
@FromBIStyle int,
@ToBIStyle int
as
set nocount on
Insert into fd_fields (iID,sName,sCaption,sSourceField,iEntityID,iFieldOption,bPersistent,bIsUsed,sHelpContextID,sTaskID ,iEditProp,iDataType,iReferenceType,iRefFldSqc,iQryFldSqc,iLength,iDecimals,bAllowNull,sDefaultValue,iRow,iStartCol,iEndCol,iInputCol,dblMin,dblMax) Select stuff(iID,1,2,@ToBIStyle) as iID ,sName,sCaption,sSourceField,stuff(iEntityID,1,2,@ToBIStyle) as iEntityID,iFieldOption,bPersistent,bIsUsed,sHelpContextID,sTaskID,iEditProp,iDataType,iReferenceType,iRefFldSqc,iQryFldSqc,iLength,iDecimals,bAllowNull,sDefaultValue,iRow,iStartCol,iEndCol,iInputCol,dblMin,dblMax from fd_fields where iEntityID=@FromBIStyle
go
/*单据类型定义,删除*/
create procedure dbo.fd_deletefields
@BIStyle int
as
set nocount on
if not((@BIStyle is null) or (@BIStyle = ''))
/*delete from dbo.FD_Fields where iEntityID=@BIStyle
else*/
delete from dbo.fd_fields where iEntityID=@BIStyle
go
if exists (select 1 from fd_transactions)
delete from fd_transactions
--FD_Sav
insert into fd_transactions (transactions_id,transactions_code,rcv_acc_id,bill_date,bill_name,check_name,book_name,sum_mny,userdefine5,fixedmonth_num,digest,exchange_rate,natural_mny,settle_flag) select '210000000000000',stuff(cSavID,1,2,'00'),fd_accdef.accdef_id,dBill_Date,cBillCode,cCheckCode,cBookCode,mMoney,cTran_Name,iMonth,cDigest,nFrat,mMoney_F,bSettle from FD_Sav,fd_accdef where FD_Sav.cAccID=fd_accdef.cAccID and isc=1 and fd_accdef.iio=0
insert into fd_transactions (transactions_id,transactions_code,rcv_acc_id,bill_date,bill_name,check_name,book_name,sum_mny,userdefine5,fixedmonth_num,digest,exchange_rate,natural_mny,settle_flag) select '260000000000000',stuff(cSavID,1,2,'00'),fd_accdef.accdef_id,dBill_Date,cBillCode,cCheckCode,cBookCode,mMoney,cTran_Name,iMonth,cDigest,nFrat,mMoney_F,bSettle from FD_Sav,fd_accdef where FD_Sav.cAccID=fd_accdef.cAccID and isc=1 and fd_accdef.iio=1
insert into fd_transactions (transactions_id,transactions_code,fixed_acc_id,bill_date,bill_name,check_name,book_name,sum_mny,userdefine5,fixedmonth_num,digest,exchange_rate,natural_mny,settle_flag) select '310000000000000',stuff(cSavID,1,2,'00'),fd_accdef.accdef_id,dBill_Date,cBillCode,cCheckCode,cBookCode,mMoney,cTran_Name,iMonth,cDigest,nFrat,mMoney_F,bSettle from FD_Sav,fd_accdef where FD_Sav.cAccID=fd_accdef.cAccID and isc=0 and fd_accdef.iio=0
insert into fd_transactions (transactions_id,transactions_code,fixed_acc_id,bill_date,bill_name,check_name,book_name,sum_mny,userdefine5,fixedmonth_num,digest,exchange_rate,natural_mny,settle_flag) select '330000000000000',stuff(cSavID,1,2,'00'),fd_accdef.accdef_id,dBill_Date,cBillCode,cCheckCode,cBookCode,mMoney,cTran_Name,iMonth,cDigest,nFrat,mMoney_F,bSettle from FD_Sav,fd_accdef where FD_Sav.cAccID=fd_accdef.cAccID and isc=0 and fd_accdef.iio=1
--FD_Fetch
insert into fd_transactions (transactions_id,transactions_code,pay_acc_id,bill_date,bill_name,check_name,book_name,sum_mny,userdefine5,fixedmonth_num,digest,exchange_rate,natural_mny) select '220000000000000',stuff(cFetID,1,2,'00'),fd_accdef.accdef_id,dBill_Date,cBillCode,cCheckCode,cBookCode,mMoney,cTran_Name,iMonth,cDigest,nFrat,mMoney_F from FD_Fetch,fd_accdef where FD_Fetch.cAccID=fd_accdef.cAccID and isc=1 and fd_accdef.iio=0
insert into fd_transactions (transactions_id,transactions_code,pay_acc_id,bill_date,bill_name,check_name,book_name,sum_mny,userdefine5,fixedmonth_num,digest,exchange_rate,natural_mny) select '270000000000000',stuff(cFetID,1,2,'00'),fd_accdef.accdef_id,dBill_Date,cBillCode,cCheckCode,cBookCode,mMoney,cTran_Name,iMonth,cDigest,nFrat,mMoney_F from FD_Fetch,fd_accdef where FD_Fetch.cAccID=fd_accdef.cAccID and isc=1 and fd_accdef.iio=1
insert into fd_transactions (transactions_id,transactions_code,fixed_acc_id,bill_date,bill_name,check_name,book_name,sum_mny,userdefine5,fixedmonth_num,digest,exchange_rate,natural_mny) select '320000000000000',stuff(cFetID,1,2,'00'),fd_accdef.accdef_id,dBill_Date,cBillCode,cCheckCode,cBookCode,mMoney,cTran_Name,iMonth,cDigest,nFrat,mMoney_F from FD_Fetch,fd_accdef where FD_Fetch.cAccID=fd_accdef.cAccID and isc=0 and fd_accdef.iio=0
insert into fd_transactions (transactions_id,transactions_code,fixed_acc_id,bill_date,bill_name,check_name,book_name,sum_mny,userdefine5,fixedmonth_num,digest,exchange_rate,natural_mny) select '340000000000000',stuff(cFetID,1,2,'00'),fd_accdef.accdef_id,dBill_Date,cBillCode,cCheckCode,cBookCode,mMoney,cTran_Name,iMonth,cDigest,nFrat,mMoney_F from FD_Fetch,fd_accdef where FD_Fetch.cAccID=fd_accdef.cAccID and isc=0 and fd_accdef.iio=1
--FD_Cred
insert into fd_transactions (transactions_id,transactions_code,rcv_acc_id,bill_date,bill_name,check_name,book_name,sum_mny,userdefine5,userdefine6,userdefine7,fixedmonth_num,digest,exchange_rate,natural_mny,irate_id,cad_id,return_date,calctype_flag,settle_flag) select '410000000000000',stuff(cCreID,1,2,'00'),fd_accdef.accdef_id,dBill_Date,cBillCode,cCheckCode,cBookCode,mMoney,cTran_Name,cEnter_Name,mMoneying,iMonth,cDigest,nFrat,mMoney_F,fd_intra.irate_id,fd_cadset.cad_id,dRet_Date,iArtyp,bSettle from FD_Cred,fd_accdef,fd_cadset,fd_intra where FD_Cred.cAccID=fd_accdef.cAccID and FD_Cred.iio=0 and FD_Cred.cIntrID=fd_intra.cIntrID and FD_Cred.cCadID=fd_cadset.cCadID
insert into fd_transactions (transactions_id,transactions_code,rcv_acc_id,bill_date,bill_name,check_name,book_name,sum_mny,userdefine5,userdefine6,userdefine7,fixedmonth_num,digest,exchange_rate,natural_mny,irate_id,cad_id,return_date,calctype_flag,settle_flag) select '450000000000000',stuff(cCreID,1,2,'00'),fd_accdef.accdef_id,dBill_Date,cBillCode,cCheckCode,cBookCode,mMoney,cTran_Name,cEnter_Name,mMoneying,iMonth,cDigest,nFrat,mMoney_F,fd_intra.irate_id,fd_cadset.cad_id,dRet_Date,iArty⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -