gz.sql

一个用VB写的财务软件源码

create table TGZ_BANK
(
  BANKCODE   VARCHAR2(2) not null,
  BANKNAME   VARCHAR2(40),
  ISFIXEDLEN NUMBER default -1,
  ZHLEN      NUMBER default 10              
);
alter table TGZ_BANK
  add primary key (BANKCODE)
  using index;

create table TGZ_BBDEFINE
(
  ID      NUMBER,
  BBMC    CHAR(20) not null,
  ITEM    CHAR(30),
  ITEMENG CHAR(20),
  ISFLXM  NUMBER not null,
  SYFLAG  CHAR(2)
);

create table TGZ_BZMZ
(
  BZMC VARCHAR2(10) not null,
  MZBH VARCHAR2(2) not null,
  MZMC VARCHAR2(10) not null,
  HSBL NUMBER(8,2)
);

create table TGZ_FORMULACAL
(
  GZLBID         NUMBER(10) not null,
  ACCOUNTYEAR    CHAR(4) not null,
  ACCOUNTPERIOD  NUMBER(5) not null,
  ORDERNUM       NUMBER(5) not null,
  NUMFL          NUMBER(5) not null,
  ITEMID         NUMBER(10) not null,
  ITEMNAME       VARCHAR2(30) not null,
  FORMVALUE      VARCHAR2(500),
  FORMVALUECHINA VARCHAR2(1000),
  ISSYSTEM       NUMBER default 0 not null
);
alter table TGZ_FORMULACAL
  add constraint TGZ_FORMULACAL_KEY primary key (ITEMID,ACCOUNTPERIOD,ACCOUNTYEAR,GZLBID)
  using index;
create unique index PK_GZ_FORMULACAL on TGZ_FORMULACAL (GZLBID,ACCOUNTYEAR,ACCOUNTPERIOD,ORDERNUM,NUMFL,ITEMNAME,FORMVALUE);

create table TGZ_GZDATA<<YEAR>>
(
  GZLBID        NUMBER not null,
  ACCOUNTPERIOD NUMBER not null,
  RYBH          VARCHAR2(20) not null,
  RYXM          CHAR(20) not null,
  BMMC          CHAR(20) not null,
  BMBH          CHAR(20) not null,
  RYLB          CHAR(20) not null,
  F1            NUMBER(8,2) default 0 not null,
  F2            NUMBER(8,2) default 0 not null,
  F3            NUMBER(8,2) default 0 not null,
  F4            NUMBER(8,2) default 0 ,
  F5            NUMBER(8,2) default 0 ,
  F6            NUMBER(8,2) default 0 not null,
  F7            NUMBER(8,2) default 0 
);
alter table TGZ_GZDATA<<YEAR>>
  add constraint TGZ_GZDATA<<YEAR>>_KEY primary key (GZLBID,ACCOUNTPERIOD,RYBH)
  using index ;
  

create table TGZ_GZDATAtemp
(
  GZLBID        NUMBER not null,
  PCID          NUMBER not null,
  ACCOUNTPERIOD NUMBER not null,
  RYBH          VARCHAR2(20) not null,
  RYXM          CHAR(20) not null,
  BMMC          CHAR(20) not null,
  BMBH          CHAR(20) not null,
  RYLB          CHAR(20) not null,
  F1            NUMBER(8,2) default 0 not null,
  F2            NUMBER(8,2) default 0 not null,
  F3            NUMBER(8,2) default 0 not null,
  F4            NUMBER(8,2) default 0 ,
  F5            NUMBER(8,2) default 0 ,
  F6            NUMBER(8,2) default 0 not null,
  F7            NUMBER(8,2) default 0 
);

create table TGZ_GZDATAP<<YEAR>>
(
  GZLBID        NUMBER not null,
  PCID          NUMBER not null,
  ACCOUNTPERIOD NUMBER not null,
  RYBH          VARCHAR2(20) not null,
  RYXM          CHAR(20) not null,
  BMMC          CHAR(20) not null,
  BMBH          CHAR(20) not null,
  RYLB          CHAR(20) not null,
  F1            NUMBER(8,2) default 0 not null,
  F2            NUMBER(8,2) default 0 not null,
  F3            NUMBER(8,2) default 0 not null,
  F4            NUMBER(8,2) default 0 ,
  F5            NUMBER(8,2) default 0 ,
  F6            NUMBER(8,2) default 0 not null,
  F7            NUMBER(8,2) default 0 
);
alter table TGZ_GZDATAP<<YEAR>>
  add constraint TGZ_GZDATAP<<YEAR>>_KEY primary key (GZLBID,ACCOUNTPERIOD,PCID,RYBH)
  using index ;

create table TGZ_GZITEM
(
  ITEMID      NUMBER not null,
  ITEMNAME    VARCHAR2(30) not null,
  DATATYPE    CHAR(4) not null,
  FIELDLENGTH NUMBER not null,
  DECIMALBITS NUMBER,
  KJKMDM      VARCHAR2(40),
  ISUSED      NUMBER default 0 not null,
  ISSYSDEF    NUMBER default 0 not null,
  ISTAKETAX   NUMBER default 0 not null
);
alter table TGZ_GZITEM
  add primary key (ITEMID)
  using index ;
alter table TGZ_GZITEM
  add constraint KEY_ITEMNAME unique (ITEMNAME)
  using index ;

create table TGZ_GZITEMSET
(
  GZLBID        NUMBER not null,
  ACCOUNTYEAR   CHAR(4) not null,
  ACCOUNTPERIOD NUMBER not null,
  ORDERNUM      NUMBER not null,
  ITEMID        NUMBER not null,
  ITEMNAME      VARCHAR2(30) not null,
  ISSHOW        NUMBER default 0,
  ISPRINT       NUMBER default 0,
  ITEMISUSED    NUMBER default 0,
  YMQL          NUMBER(2) default 0,
  YLQL          NUMBER(2) default 0,
  FORMULA       CHAR(60),
  ISHZ          NUMBER(2) default 0
);

create unique index PK_GZ_GZITEMSET on TGZ_GZITEMSET (GZLBID,ACCOUNTYEAR,ACCOUNTPERIOD,ITEMID);

create table TGZ_GZLB
(
  GZLBID        NUMBER not null,
  GZLBNAME      VARCHAR2(40) not null,
  BEGINDATE     DATE not null,
  BEGINYEAR     CHAR(4) not null,
  BEGINPERIOD   NUMBER not null,
  CURRENTYEAR   CHAR(4) not null,
  CURRENTPERIOD NUMBER not null,
  CURRENCYNAME  VARCHAR2(10),
  ISBANK        NUMBER default 0,
  ISTAX         NUMBER default 0,
  TAILTYPE      VARCHAR2(10) not null,
  TAXBASE       NUMBER(15,2) default 0 not null,
  ISCOUNT       NUMBER(3) default 0 not null
);
alter table TGZ_GZLB 
  add primary key (GZLBID)
  using index ;

create table TGZ_HELPINFOSHOW
(
  GZLBID       NUMBER not null,
  HELPTYPE     VARCHAR2(40) not null,
  ORDERNUM     NUMBER not null,
  INFOSOURCE   VARCHAR2(20),
  FIELDNAMEENG VARCHAR2(20),
  FIELDNAME    VARCHAR2(30)
);

create unique index PK_GZ_HELPINFOSHOW on TGZ_HELPINFOSHOW (GZLBID,HELPTYPE,ORDERNUM);

create table TGZ_PERSONINFOSHOW
(
  GZLBID       NUMBER not null,
  ORDERNUM     NUMBER not null,
  FIELDNAMEENG VARCHAR2(20),
  FIELDNAME    VARCHAR2(30)
);

create unique index PK_GZ_PERSONINFOSHOW on TGZ_PERSONINFOSHOW (GZLBID,ORDERNUM);

create table TGZ_RYLB
(
  ID   NUMBER(2) not null,
  LBMC CHAR(20) not null
);
create unique index PK_GZ_RYLB on TGZ_RYLB (ID);

create table TGZ_TAXSET
(
  GZLBID  NUMBER not null,
  DUPPER  NUMBER(8,2),
  DLOWER  NUMBER(8,2),
  DTAX    NUMBER(5,2),
  DDEDUCT NUMBER(8,2),
  ICLASS  NUMBER not null
);
alter table TGZ_TAXSET
  add constraint PK_GZ_TAXSET primary key (GZLBID,ICLASS)
  using index ;

create table TGZ_TAXTEMP
(
  RYBH  VARCHAR2(20) not null,
  YSSUM NUMBER(8,2) not null,
  JSSUM NUMBER(8,2) not null
);

create table Tgz_TZZRY /*14职工岗位变动表*/
(
  ZGBH    CHAR(20) not null,
  BMBH    CHAR(12),
  BMMC    CHAR(20),
  ZGXM    CHAR(20),
  GZZH    CHAR(20),
  ZZ      CHAR(4),
  SFZH    CHAR(18),
  XB      CHAR(2),
  MZ      CHAR(20),
  JGBH    CHAR(6),
  JG      CHAR(40),
  CSRQ    DATE,
  NL      NUMBER default 0,
  HY      CHAR(10),
  GRSF    CHAR(10),
  ZZMM    CHAR(10),
  SHBZH   CHAR(20),
  YLZHH   CHAR(20),
  GRYHZH  CHAR(20),
  GRYHDH  CHAR(2),
  WHCD    CHAR(10),
  BYYX    CHAR(40),
  BYSJ    DATE,
  ZY      CHAR(20),
  XW      CHAR(10),
  YZ      CHAR(10),
  CJGZSJ  DATE,
  JRBDWSJ DATE,
  GL      NUMBER default 0,
  RYFL    CHAR(20),
  GWGZ    CHAR(20),
  JSZC    CHAR(10),
  GZJB    CHAR(10),
  XZZW    CHAR(20),
  JTZZ    CHAR(40),
  YB      CHAR(6),
  DHHM    CHAR(40),
  HKSZD   CHAR(40),
  HKXZ    CHAR(10),
  GADQS   CHAR(10),
  LGQK    CHAR(10),
  LGSJ    DATE,
  XGFL    CHAR(10),
  XGSJ    DATE,
  BQYGL   NUMBER default 0,
  HTBZ    NUMBER default 0,
  GZLBID  NUMBER,
  KHH     CHAR(25),
  ZPFILE  CHAR(80),
  DADH    CHAR(20),
  KMDM    CHAR(40),
  TFNY    CHAR(10),
  hfny    char(10)
)
tablespace CW_TS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 10K
    next 10K
    minextents 1
    maxextents 121
    pctincrease 0
  );
alter table Tgz_TZZRY
  add constraint PK_gz_TZZRY primary key (ZGBH)
  using index 
  tablespace CW_TS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 10K
    next 10K
    minextents 1
    maxextents 121
    pctincrease 0
  );

create table TGZ_ZZPZLB
(
  ID       NUMBER not null,
  NAME     VARCHAR2(20) not null,
  ISSYSTEM NUMBER(4) default 0 not null
);

alter table TGZ_ZZPZLB
  add constraint TGZ_ZZPZLB_KEY primary key (ID)
  disable;
create unique index TGZ_ZZPZLB_INDEX on TGZ_ZZPZLB (NAME);

create table TGZ_ZZSETCALSS
(
  ID        NUMBER not null,
  SETLISTID NUMBER not null,
  CKMDM     VARCHAR2(40) not null,
  CBMDM     VARCHAR2(4),
  CXMDM     VARCHAR2(40),
  CFX       CHAR(2) not null,
  ITEMID    NUMBER not null,
  bBMDM     VARCHAR2(4),
  CRYLB     VARCHAR2(20),
  JTYZ      NUMBER(5,4) default 1 not null,
  ZY        VARCHAR2(200)
);
alter table TGZ_ZZSETCALSS
  add constraint TGZ_ZZSETCALSS_KEY primary key (ID)
  using index ;

create table TGZ_ZZSETLIST
(
  ID           NUMBER not null,
  GZLBID       NUMBER not null,
  ZZLBID       NUMBER not null,
  ZZDATE       DATE,
  PZBH         CHAR(4)
);
alter table TGZ_ZZSETLIST
  add constraint pk_TGZ_ZZSETLIST primary key (ID)
  using index ;
create unique index TGZ_ZZSETLIST_INDEX on TGZ_ZZSETLIST (ZZLBID,GZLBID);

/* 以下是初试化数据 */

insert into TGZ_BANK (BANKCODE, BANKNAME, ISFIXEDLEN, ZHLEN) values ('01', '工商银行', -1, 18);
insert into TGZ_BANK (BANKCODE, BANKNAME, ISFIXEDLEN, ZHLEN) values ('02', '交通银行', -1, 18);

insert into TGZ_BZMZ (BZMC, MZBH, MZMC, HSBL) values ('人民币', '01', '壹分', .01);
insert into TGZ_BZMZ (BZMC, MZBH, MZMC, HSBL) values ('人民币', '02', '贰分', .02);
insert into TGZ_BZMZ (BZMC, MZBH, MZMC, HSBL) values ('人民币', '03', '伍分', .05);
insert into TGZ_BZMZ (BZMC, MZBH, MZMC, HSBL) values ('人民币', '04', '壹角', .1);
insert into TGZ_BZMZ (BZMC, MZBH, MZMC, HSBL) values ('人民币', '05', '贰角', .2);
insert into TGZ_BZMZ (BZMC, MZBH, MZMC, HSBL) values ('人民币', '06', '伍角', .5);
insert into TGZ_BZMZ (BZMC, MZBH, MZMC, HSBL) values ('人民币', '07', '壹圆', 1);
insert into TGZ_BZMZ (BZMC, MZBH, MZMC, HSBL) values ('人民币', '08', '贰圆', 2);
insert into TGZ_BZMZ (BZMC, MZBH, MZMC, HSBL) values ('人民币', '09', '伍圆', 5);
insert into TGZ_BZMZ (BZMC, MZBH, MZMC, HSBL) values ('人民币', '10', '拾圆', 10);
insert into TGZ_BZMZ (BZMC, MZBH, MZMC, HSBL) values ('人民币', '13', '贰拾圆', 20);
insert into TGZ_BZMZ (BZMC, MZBH, MZMC, HSBL) values ('人民币', '11', '伍拾圆', 50);
insert into TGZ_BZMZ (BZMC, MZBH, MZMC, HSBL) values ('人民币', '12', '壹佰圆', 100);

insert into TGZ_GZITEM (ITEMID, ITEMNAME, DATATYPE, FIELDLENGTH, DECIMALBITS, KJKMDM, ISUSED, ISSYSDEF, ISTAKETAX) values (1, '应发合计', '数字', 8, 2, null, -1, -1, 0);
insert into TGZ_GZITEM (ITEMID, ITEMNAME, DATATYPE, FIELDLENGTH, DECIMALBITS, KJKMDM, ISUSED, ISSYSDEF, ISTAKETAX) values (2, '应扣合计', '数字', 8, 2, null, -1, -1, 0);
insert into TGZ_GZITEM (ITEMID, ITEMNAME, DATATYPE, FIELDLENGTH, DECIMALBITS, KJKMDM, ISUSED, ISSYSDEF, ISTAKETAX) values (3, '实发工资', '数字', 8, 2, null, -1, -1, 0);
insert into TGZ_GZITEM (ITEMID, ITEMNAME, DATATYPE, FIELDLENGTH, DECIMALBITS, KJKMDM, ISUSED, ISSYSDEF, ISTAKETAX) values (4, '上次结余', '数字', 8, 2, null, -1, -1, 0);
insert into TGZ_GZITEM (ITEMID, ITEMNAME, DATATYPE, FIELDLENGTH, DECIMALBITS, KJKMDM, ISUSED, ISSYSDEF, ISTAKETAX) values (5, '本次扣零', '数字', 8, 2, null, -1, -1, 0);
insert into TGZ_GZITEM (ITEMID, ITEMNAME, DATATYPE, FIELDLENGTH, DECIMALBITS, KJKMDM, ISUSED, ISSYSDEF, ISTAKETAX) values (6, '收入额', '数字', 8, 2, null, -1, -1, 0);
insert into TGZ_GZITEM (ITEMID, ITEMNAME, DATATYPE, FIELDLENGTH, DECIMALBITS, KJKMDM, ISUSED, ISSYSDEF, ISTAKETAX) values (7, '个人所得税', '数字', 8, 2, null, -1, -1, -1);

INSERT INTO tGZ_Rylb(id,lbmc) VALUES(1,'管理人员');
INSERT INTO tGZ_Rylb(id,lbmc) VALUES(2,'生产人员');
INSERT INTO tGZ_Rylb(id,lbmc) VALUES(3,'辅助人员');

INSERT INTO tGZ_zzPZLB(id,Name,isSystem) VALUES(1,'工资分摊',1);
INSERT INTO tGZ_zzPZLB(id,Name,isSystem) VALUES(2,'计提福利费',1);