fx.sql

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

    next 10K
    minextents 1
    maxextents 121
    pctincrease 0
  );
create unique index PK_FX_RATEUNIT on TFX_RATEUNIT (CYEAR,UNITNAME)
  tablespace CW_TS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 10K
    next 10K
    minextents 1
    maxextents 121
    pctincrease 0
  );
create table TFX_REPORT
(
  CYEAR         CHAR(4) not null,
  VCREPORTCODE  VARCHAR2(10) not null,
  VCREPORTNAME  VARCHAR2(50) not null,
  CREPORTTYPE   CHAR(4) not null,
  BTOTAL        NUMBER default -1,
  BCOLLECT      NUMBER default -1,
  BMERGE        NUMBER default -1,
  BANALYSIS     NUMBER default 0,
  SISERIAL      NUMBER not null,
  ID            NUMBER default 0,
  Typecode      char(2) default '00',
  BABSOLUTENESS NUMBER default 0,
  BBASE         NUMBER default 0,
  BCIRCLE       NUMBER default 0,
  BCOMPARE      NUMBER default 0,
  BSTRUCTRUE    NUMBER default 0       /*是否结构分析*/
)
tablespace CW_TS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 10K
    next 10K
    minextents 1
    maxextents 121
    pctincrease 0
  );
create unique index PK_FX_REPORT on TFX_REPORT (CYEAR,VCREPORTCODE)
  tablespace CW_TS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 10K
    next 10K
    minextents 1
    maxextents 121
    pctincrease 0
  );
create table TFX_REPORTDATA<<YEAR>>
(
  REPORTCODE VARCHAR2(10) not null,
  ENTERCODE  VARCHAR2(25) not null,
  MONTH      NUMBER not null,
  IROWLABEL  NUMBER not null,
  COLLABEL   NUMBER not null,
  CELLDATA   NUMBER(15,4) default 0       /*单元数据*/
)
tablespace CW_TS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 40K
    next 10K
    minextents 1
    maxextents 121
    pctincrease 0
  );
create unique index PK_FX_REPORTDATA<<YEAR>> on TFX_REPORTDATA<<YEAR>> (REPORTCODE,ENTERCODE,MONTH,IROWLABEL,COLLABEL)
  tablespace CW_TS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 50K
    next 10K
    minextents 1
    maxextents 121
    pctincrease 0
  );
create table TFX_REPORTMODE
(
  CYEAR        CHAR(4) not null,
  CMONTH       CHAR(2) not null,
  VCREPORTCODE VARCHAR2(50) not null,
  LCONTENT     LONG RAW,
  LENGTH       NUMBER(10) default 0 not null
)
tablespace CW_TS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 300K
    next 10K
    minextents 1
    maxextents 121
    pctincrease 0
  );
create unique index PK_FX_REPORTMODE on TFX_REPORTMODE (CYEAR,CMONTH,VCREPORTCODE)
  tablespace CW_TS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 10K
    next 10K
    minextents 1
    maxextents 121
    pctincrease 0
  );
create table TFX_SUBJECT
(
  CYEAR       CHAR(4) not null,
  SUBJECTCODE VARCHAR2(40) not null,
  SUBJECTNAME VARCHAR2(40) not null,
  FORMULA     VARCHAR2(200)
)
tablespace CW_TS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 10K
    next 10K
    minextents 1
    maxextents 121
    pctincrease 0
  );
create unique index PK_FX_SUBJECT on TFX_SUBJECT (CYEAR,SUBJECTCODE)
  tablespace CW_TS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 10K
    next 10K
    minextents 1
    maxextents 121
    pctincrease 0
  );
create table TFX_XJKM
(
  CYEAR     CHAR(4) not null,
  KMCODE    VARCHAR2(40) not null,
  KMNAME    VARCHAR2(40) not null,
  YEFX      CHAR(4) not null,
  DIRECTION CHAR(4) not null
)
tablespace CW_TS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 10K
    next 10K
    minextents 1
    maxextents 121
    pctincrease 0
  );
create unique index PK_FX_XJKM on TFX_XJKM (CYEAR,KMCODE,DIRECTION)
  tablespace CW_TS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 10K
    next 10K
    minextents 1
    maxextents 121
    pctincrease 0
  );

delete TFX_RATE where CYEAR='<<YEAR>>';
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', 'DB', '01', '权益净利率', '%', '资产净利率*权益乘数', 1, 'ZB("DB0101")*ZB("DB0102")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', 'DB', '01010101', '净利润', '元', '损益表的净利润', 1, 'BJQS("0000","jb020","本年","同期","24","24","4","4")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', 'DB', '010102', '总资产周转率', '%', '销售净额/资产平均总额', 1, 'ZB("DB01010102")/ZB("DB01010201")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', 'DB', '01010201', '资产平均总额', '元', '（期初资产总额+期末资产总额）/2', 1, '(BJQS("0000","jb010","本年","同期","34","34","3","3")+BJQS("0000","jb010","本年","同期","34","34","4","4"))/2');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', 'DB', '0102', '权益乘数', '%', '1/（1-资产负债率）', 1, '1/(1-ZB("HY05"))');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', 'HY', '04', '资本保值增值率', '%', '期末所有者权益总额/期初所有者权益总额', 1, null);
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', 'HY', '09', '社会贡献率', '%', '企业社会贡献总额/平均资产总额', 1, null);
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', 'HY', '10', '社会积累率', '%', '上交国家财政总额/企业社会贡献总额', 1, null);
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '06', '08', '每股净资产', '元', '股东权益/普通股平均股数', 1, 'BJQS("0000","jb010","本年","同期","34","34","8","8")/BN("3101","LJYE","DF")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '02', '02', '存货周转天数', '天', '天数/(销售成本/((期初存货+期末存货)/2))', 1, '30/ZB("0201")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '02', '03', '应收账款周转率', '次', '赊销收入净额/((期初应收账款余额+期末应收账款余额)/2)', 1, 'ZB("DB01010102")/(BJQS("0000","jb010","本年","同期","7","7","3","3")+BJQS("0000","jb010","本年","同期","7","7","4","4"))/2');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '02', '04', '应收账款天数', '天', '天数/(销售收入净额/((期初应收账款余额+期末应收账款余额)/2))', 1, '360/ZB("0203")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '02', '05', '营业周期', '天', '天数/(销售成本/((期初存货+期末存货)/2))+天数/(销售收入/((期初应收帐款余额+期末应收帐款余额)/2))', 1, '30/(ZB("0201")/ZB("0203"))');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '02', '07', '总资产周转率', '次', '销售收入净额/((期初资产总额+期末资产总额)/2)', 1, 'ZB("DB01010102")/((BJQS("0000","jb010","本年","同期","34","34","3","3")+BJQS("0000","jb010","本年","同期","34","34","4","4"))/2)');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '03', '02', '产权比率', '%', '负债总额/所有者权益', 1, '(BJQS("0000","jb010","本年","同期","15","15","8","8")+BJQS("0000","jb010","本年","同期","22","22","8","8"))/BJQS("0000","jb010","本年","同期","34","34","8","8")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '04', '01', '销售净利率', '%', '净利润/销售收入', 1, '(BN("3131","LJYE","DF")+BN("3131-99","LJYE","JF"))/ZB("DB01010102")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '04', '05', '资本金利润率', '%', '利润总额/资本总额', 1, 'BJQS("0000","jb020","本年","同期","21","21","4","4")/BJQS("0000","jb010","本年","同期","26","26","8","8")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '05', '01', '经费自给率', '%', '(事业收入+经营收入+附属单位上缴收入+其它收入)/(事业支出+经营支出)', 1, null);
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '01', '01', '流动比率', '%', '流动资产/流动负债', 1, 'BJQS("0000","jb010","本年","同期","15","15","4","4")/BJQS("0000","jb010","本年","同期","15","15","8","8")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '06', '01', '每股收益', '元', '净利润/普通股平均股数', 1, 'BJQS("0000","jb020","本年","同期","24","24","4","4")/BN("3101","LJYE","DF")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '06', '02', '市盈率', '%', '每股市价/每股盈余', 1, null);
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '06', '03', '股利与市价比率', '%', '每股股利/股利市价', 1, null);
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '06', '04', '股利分派率', '%', '每股股利/每股收益额', 1, null);
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '06', '05', '每股账面价值', '天', '(股东权益总额-优先股权益)/普通股股数', 1, 'BJQS("0000","jb010","本年","同期","34","34","8","8")/BN("3101","LJYE","DF")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', 'DB', '010101', '销售净利率', '%', '净利润/销售净额', 1, 'ZB("DB01010101")/ZB("DB01010102")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', 'DB', '0101', '资产净利率', '%', '销售净利率*总资产周转率', 1, 'ZB("DB010101")*ZB("DB010102")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', 'DB', '010201', '资产负债率', '%', '（负债总额/资产总额）*100%', 1, '(BJQS("0000","jb010","本年","同期","15","15","8","8")+BJQS("0000","jb010","本年","同期","22","22","8","8"))/BJQS("0000","jb010","本年","同期","34","34","4","4")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '01', '02', '速动比率', '%', '(流动资产-存货-待摊费用-待处理流动资产损失)/流动负债*100', 1, '(BJQS("0000","jb010","本年","同期","15","15","4","4")-BJQS("0000","jb010","本年","同期","10","10","4","4")-BJQS("0000","jb010","本年","同期","11","11","4","4")-BJQS("0000","jb010","本年","同期","12","12","4","4"))/BJQS("0000","jb010","本年","同期","15","15","8","8")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', 'DB', '01010102', '销售净额', '元', '主营业务收入+其他业务收入', 0, 'BN("5101","LJYE","DF")+BN("5101-99","LJYE","JF")+BN("5102","LJYE","DF")+BN("5102-99","LJYE","JF")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '02', '01', '存货周转率（次数）', '次', '销售成本/((期初存货+期末存货)/2)', 1, '(BN("5401","YE","JF")+BN("5401-99","LJYE","DF"))/(BJQS("0000","jb010","本年","同期","10","10","3","3")+BJQS("0000","jb010","本年","同期","10","10","4","4"))');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '02', '06', '流动资产周转率', '次', '销售收入净额/((期初流动资产+期末流动资产)/2)', 1, 'ZB("DB01010102")/((BJQS("0000","jb010","本年","同期","15","15","3","3")+BJQS("0000","jb010","本年","同期","15","15","4","4"))/2)');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '03', '01', '资产负债率', '%', '负债总额/资产总额', 1, '(BJQS("0000","jb010","本年","同期","15","15","8","8")+BJQS("0000","jb010","本年","同期","22","22","8","8"))/BJQS("0000","jb010","本年","同期","34","34","4","4")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '03', '03', '有形净值债务率', '%', '负债总额/(净资产-无形资产)', 1, '(BJQS("0000","jb010","本年","同期","15","15","8","8")+BJQS("0000","jb010","本年","同期","22","22","8","8"))/(BJQS("0000","jb010","本年","同期","33","33","8","8")-BJQS("0000","jb010","本年","同期","27","27","4","4"))');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '03', '04', '已获利息倍数', '%', '息税前利润/债务利息', 1, '(BJQS("0000","jb020","本年","同期","21","21","4","4")-BN("5503-01","LJYE","JF"))/BN("5503-01","LJYE","JF")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '05', '02', '资产负债率', '%', '负债总额/资产总额', 1, '(BJQS("0000","jb010","本年","同期","15","15","8","8")+BJQS("0000","jb010","本年","同期","22","22","8","8"))/BJQS("0000","jb010","本年","同期","34","34","4","4")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '06', '06', '产权比率', '%', '负债总额/股东权益', 1, '(BJQS("0000","jb010","本年","同期","15","15","8","8")+BJQS("0000","jb010","本年","同期","22","22","8","8"))/BJQS("0000","jb010","本年","同期","34","34","8","8")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '06', '07', '净值报酬率', '%', '净利润/股东权益', 1, 'BJQS("0000","jb020","本年","同期","24","24","4","4")/BJQS("0000","jb010","本年","同期","33","33","8","8")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '04', '02', '销售毛利率', '%', '(销售收入-销售成本)/销售收入', 1, '(BJQS("0000","jb020","本年","同期","10","10","4","4")+BJQS("0000","jb020","本年","同期","11","11","4","4"))/ZB("DB01010102")');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '04', '03', '资产净利率', '%', '净利润/((期初资产总额+期末资产总额)/2)', 1, 'BJQS("0000","jb020","本年","同期","24","24","4","4")/(BJQS("0000","jb010","本年","同期","34","34","3","3")+BJQS("0000","jb010","本年","同期","34","34","3","3"))/2');
insert into TFX_RATE (CYEAR, TYPECODE, RATECODE, RATENAME, UNIT, FORMULA, ISSYSDEF, QSFORMULA)
values ('<<YEAR>>', '04', '04', '净资产收益率', '%', '净利润/((期初净资产+期末净资产)/2)', 1, 'BJQS("0000","jb020","本年","同期","24","24","4","4")/(BJQS("0000","jb010","本年","同期","34","34","7","7")+BJQS("0000","jb010","本年","同期","34","34","7","7"))/2');

delete TFX_RATETYPE where CYEAR='<<YEAR>>';
insert into TFX_RATETYPE (CYEAR, TYPECODE, TYPENAME, ISSYSDEF)
values ('<<YEAR>>', '02', '资产管理比率', -1);
insert into TFX_RATETYPE (CYEAR, TYPECODE, TYPENAME, ISSYSDEF)
values ('<<YEAR>>', '03', '负债比率', -1);
insert into TFX_RATETYPE (CYEAR, TYPECODE, TYPENAME, ISSYSDEF)
values ('<<YEAR>>', '04', '盈利能力比率', -1);
insert into TFX_RATETYPE (CYEAR, TYPECODE, TYPENAME, ISSYSDEF)
values ('<<YEAR>>', '05', '事业单位指标', -1);
insert into TFX_RATETYPE (CYEAR, TYPECODE, TYPENAME, ISSYSDEF)
values ('<<YEAR>>', '06', '股份制企业指标', -1);
insert into TFX_RATETYPE (CYEAR, TYPECODE, TYPENAME, ISSYSDEF)
values ('<<YEAR>>', 'DB', '杜邦分析指标', -1);
insert into TFX_RATETYPE (CYEAR, TYPECODE, TYPENAME, ISSYSDEF)
values ('<<YEAR>>', 'HY', '行业财务指标', -1);
insert into TFX_RATETYPE (CYEAR, TYPECODE, TYPENAME, ISSYSDEF)
values ('<<YEAR>>', '01', '变现能力比率', -1);