MES_Client/TGMES/Core.StlMes.Client.Sale.SaleFundMgt/Data/CreateDb/CreateDb.sql

-----------------------------------------------------

spool xsfun.log

prompt
prompt Creating table SEL_FUND_ACCT_RELATION
prompt =====================================
prompt
create table SEL_FUND_ACCT_RELATION
(
  RELATION           VARCHAR2(10),
  PACT_REA_NAME      VARCHAR2(100),
  PACT_REA_NAME_SHOW VARCHAR2(100),
  SECONDACCOUNT      VARCHAR2(100),
  PRIORITY           VARCHAR2(1),
  IMPORTTIME         VARCHAR2(20),
  IMPORTOR           VARCHAR2(60),
  ISVALID            VARCHAR2(1),
  DELETEOR           VARCHAR2(60),
  DELETETIME         VARCHAR2(20),
  LASTUPDATETIME     VARCHAR2(20)
)
;

prompt
prompt Creating table SEL_FUND_BUSINESSINFO
prompt ====================================
prompt
create table SEL_FUND_BUSINESSINFO
(
  BUSISEQ        VARCHAR2(50) not null,
  BUSITYPE       VARCHAR2(100),
  BALANCESUBJECT VARCHAR2(40),
  BUYERCODE      VARCHAR2(40),
  SECONDACCOUNT  VARCHAR2(100),
  PACTNO         VARCHAR2(100),
  MONEY          NUMBER,
  IMPORTOR       VARCHAR2(40),
  IMPORTTIME     VARCHAR2(20),
  REMARK         VARCHAR2(4000)
)
;
comment on table SEL_FUND_BUSINESSINFO
  is '业务操作日志表';
comment on column SEL_FUND_BUSINESSINFO.BUSISEQ
  is '进出序列';
comment on column SEL_FUND_BUSINESSINFO.BUSITYPE
  is '业务类型';
comment on column SEL_FUND_BUSINESSINFO.BALANCESUBJECT
  is '结算主体，暂定分新钢销售，北京办';
comment on column SEL_FUND_BUSINESSINFO.BUYERCODE
  is '客户账户名称，同客户编码，同现有客户表中的编码';
comment on column SEL_FUND_BUSINESSINFO.SECONDACCOUNT
  is '二级账户名称';
comment on column SEL_FUND_BUSINESSINFO.PACTNO
  is '合同账户名，同合同号';
comment on column SEL_FUND_BUSINESSINFO.MONEY
  is '金额';
comment on column SEL_FUND_BUSINESSINFO.IMPORTOR
  is '录入人';
comment on column SEL_FUND_BUSINESSINFO.IMPORTTIME
  is '录入时间';
create index IDX_BUSITIME on SEL_FUND_BUSINESSINFO (IMPORTTIME);
create index IDX_BUSITYPE on SEL_FUND_BUSINESSINFO (BUSITYPE);

prompt
prompt Creating table SEL_FUND_CUST_ACCOUNT
prompt ====================================
prompt
create table SEL_FUND_CUST_ACCOUNT
(
  BALANCESUBJECT VARCHAR2(40) not null,
  BUYERCODE      VARCHAR2(40) not null,
  TOTALMONEY     NUMBER,
  LEAVEMONEY     NUMBER,
  IMPORTOR       VARCHAR2(40),
  IMPORTTIME     VARCHAR2(20),
  UPDATETIME     VARCHAR2(20)
)
;
comment on table SEL_FUND_CUST_ACCOUNT
  is '客户账户信息表';
comment on column SEL_FUND_CUST_ACCOUNT.BALANCESUBJECT
  is '结算主体，暂定分新钢销售，北京办';
comment on column SEL_FUND_CUST_ACCOUNT.BUYERCODE
  is '客户账户名称，同客户编码，同现有客户表中的编码';
comment on column SEL_FUND_CUST_ACCOUNT.TOTALMONEY
  is '账户总资金';
comment on column SEL_FUND_CUST_ACCOUNT.LEAVEMONEY
  is '账户剩余资金';
comment on column SEL_FUND_CUST_ACCOUNT.IMPORTOR
  is '录入人';
comment on column SEL_FUND_CUST_ACCOUNT.IMPORTTIME
  is '录入时间';
comment on column SEL_FUND_CUST_ACCOUNT.UPDATETIME
  is '更新时间';
alter table SEL_FUND_CUST_ACCOUNT
  add constraint PK_SEL_FUND_CUST_ACCOUNT primary key (BALANCESUBJECT, BUYERCODE);

prompt
prompt Creating table SEL_FUND_CUST_FUNDINOUT
prompt ======================================
prompt
create table SEL_FUND_CUST_FUNDINOUT
(
  BUSISEQ        VARCHAR2(50) not null,
  BALANCESUBJECT VARCHAR2(40),
  BUYERCODE      VARCHAR2(40),
  INOUTMONEY     NUMBER,
  INOUTTIME      VARCHAR2(20),
  FUNDTYPE       VARCHAR2(20),
  BILLNO         VARCHAR2(40),
  ISVALID        VARCHAR2(1) default '0',
  REMARK         VARCHAR2(400),
  IMPORTOR       VARCHAR2(40),
  IMPORTTIME     VARCHAR2(20),
  LEAVEMONEY     NUMBER
)
;
comment on table SEL_FUND_CUST_FUNDINOUT
  is '客户总账资金进出表，同实际的财务资金进出明细';
comment on column SEL_FUND_CUST_FUNDINOUT.BUSISEQ
  is '进出序列';
comment on column SEL_FUND_CUST_FUNDINOUT.BALANCESUBJECT
  is '结算主体';
comment on column SEL_FUND_CUST_FUNDINOUT.BUYERCODE
  is '客户账户名';
comment on column SEL_FUND_CUST_FUNDINOUT.INOUTMONEY
  is '进出金额，+为进账，-为出账';
comment on column SEL_FUND_CUST_FUNDINOUT.INOUTTIME
  is '进出账时间';
comment on column SEL_FUND_CUST_FUNDINOUT.FUNDTYPE
  is '如汇票，电汇，期票，进账单，其他';
comment on column SEL_FUND_CUST_FUNDINOUT.BILLNO
  is 'BILLNO';
comment on column SEL_FUND_CUST_FUNDINOUT.ISVALID
  is '是否有效,0有效，1无效';
comment on column SEL_FUND_CUST_FUNDINOUT.REMARK
  is '备注';
comment on column SEL_FUND_CUST_FUNDINOUT.IMPORTOR
  is '录入人';
comment on column SEL_FUND_CUST_FUNDINOUT.IMPORTTIME
  is '录入时间';
comment on column SEL_FUND_CUST_FUNDINOUT.LEAVEMONEY
  is '进，出钱账户剩余金额';
alter table SEL_FUND_CUST_FUNDINOUT
  add constraint PK_SEL_FUND_CUST_FUNDINOUT primary key (BUSISEQ);
create index IDX_CUST_FUNDINOUT on SEL_FUND_CUST_FUNDINOUT (BALANCESUBJECT, BUYERCODE);

prompt
prompt Creating table SEL_FUND_CUST_USEDDETAIL
prompt =======================================
prompt
create table SEL_FUND_CUST_USEDDETAIL
(
  TRANS_SEQ      VARCHAR2(40) not null,
  TRANS_TYPE     VARCHAR2(1) not null,
  BALANCESUBJECT VARCHAR2(40),
  BUYERCODE      VARCHAR2(40),
  MONEY          NUMBER,
  LEAVEMONEY     NUMBER,
  OTHERACCOUNT   VARCHAR2(40),
  TRANS_TIME     VARCHAR2(20),
  TRANS_PERSON   VARCHAR2(40),
  REMARK         VARCHAR2(400),
  UPDATETIME     VARCHAR2(20)
)
;
comment on table SEL_FUND_CUST_USEDDETAIL
  is '客户账户资金使用记录，主要在各种调拨，包括人工调拨和系统调拨';
comment on column SEL_FUND_CUST_USEDDETAIL.TRANS_SEQ
  is '序列号';
comment on column SEL_FUND_CUST_USEDDETAIL.TRANS_TYPE
  is '类型，转进or转出,0 进 1出';
comment on column SEL_FUND_CUST_USEDDETAIL.BALANCESUBJECT
  is '结算主体';
comment on column SEL_FUND_CUST_USEDDETAIL.BUYERCODE
  is '客户编码';
comment on column SEL_FUND_CUST_USEDDETAIL.MONEY
  is '发生金额';
comment on column SEL_FUND_CUST_USEDDETAIL.LEAVEMONEY
  is '发生前客户的剩余资金';
comment on column SEL_FUND_CUST_USEDDETAIL.OTHERACCOUNT
  is '另一方账户名称';
comment on column SEL_FUND_CUST_USEDDETAIL.TRANS_TIME
  is '资金使用时间';
comment on column SEL_FUND_CUST_USEDDETAIL.TRANS_PERSON
  is '资金使用人';
comment on column SEL_FUND_CUST_USEDDETAIL.REMARK
  is '备注';
comment on column SEL_FUND_CUST_USEDDETAIL.UPDATETIME
  is '最后更新时间';
alter table SEL_FUND_CUST_USEDDETAIL
  add constraint PK_SEL_FUND_CUST_USEDDETAIL primary key (TRANS_SEQ, TRANS_TYPE);

prompt
prompt Creating table SEL_FUND_PACT_ACCOUNT
prompt ====================================
prompt
create table SEL_FUND_PACT_ACCOUNT
(
  BALANCESUBJECT VARCHAR2(40) not null,
  BUYERCODE      VARCHAR2(40) not null,
  SECONDACCOUNT  VARCHAR2(100) not null,
  PACTNO         VARCHAR2(100) not null,
  TOTALMONEY     NUMBER,
  LEAVEMONEY     NUMBER,
  PACT_BEGINDATE VARCHAR2(20),
  ISINVOINCE     VARCHAR2(1) default '0',
  IMPORTOR       VARCHAR2(40),
  IMPORTTIME     VARCHAR2(20),
  UPDATETIME     VARCHAR2(20)
)
;
comment on column SEL_FUND_PACT_ACCOUNT.BALANCESUBJECT
  is '结算主体';
comment on column SEL_FUND_PACT_ACCOUNT.BUYERCODE
  is '客户账户名称';
comment on column SEL_FUND_PACT_ACCOUNT.SECONDACCOUNT
  is '二级账户名';
comment on column SEL_FUND_PACT_ACCOUNT.PACTNO
  is '合同账户名，同合同号';
comment on column SEL_FUND_PACT_ACCOUNT.TOTALMONEY
  is '总资金';
comment on column SEL_FUND_PACT_ACCOUNT.LEAVEMONEY
  is '剩余资金';
comment on column SEL_FUND_PACT_ACCOUNT.PACT_BEGINDATE
  is '合同生效日期';
comment on column SEL_FUND_PACT_ACCOUNT.ISINVOINCE
  is '是否结算关闭';
comment on column SEL_FUND_PACT_ACCOUNT.IMPORTOR
  is '录入人';
comment on column SEL_FUND_PACT_ACCOUNT.IMPORTTIME
  is '录入时间';
alter table SEL_FUND_PACT_ACCOUNT
  add constraint PK_SEL_FUND_PACT_ACCOUNT primary key (BALANCESUBJECT, BUYERCODE, SECONDACCOUNT, PACTNO);

prompt
prompt Creating table SEL_FUND_PACT_FUNDUSEDETAIL
prompt ==========================================
prompt
create table SEL_FUND_PACT_FUNDUSEDETAIL
(
  TRANS_SEQ      VARCHAR2(40) not null,
  TRANS_TYPE     VARCHAR2(40),
  BALANCESUBJECT VARCHAR2(40),
  BUYERCODE      VARCHAR2(40),
  SECONDACCOUNT  VARCHAR2(100),
  PACTNO         VARCHAR2(100),
  ASKPLAN_ID     VARCHAR2(40),
  WEIGHT         NUMBER,
  MONEY          NUMBER,
  ASKPLAN_MONEY  NUMBER,
  TRANS_MONEY    NUMBER,
  TRANS_TIME     VARCHAR2(20),
  ISMONEYVALID   VARCHAR2(20),
  TRANS_PERSON   VARCHAR2(40),
  REMARK         VARCHAR2(400),
  UPDATETIME     VARCHAR2(20)
)
;
comment on table SEL_FUND_PACT_FUNDUSEDETAIL
  is '合同资金使用，下车，装车，结算，合同生效记录明细';
comment on column SEL_FUND_PACT_FUNDUSEDETAIL.TRANS_SEQ
  is '序列';
comment on column SEL_FUND_PACT_FUNDUSEDETAIL.TRANS_TYPE
  is '合同生效，下车，装车，结算';
comment on column SEL_FUND_PACT_FUNDUSEDETAIL.BALANCESUBJECT
  is '结算主体';
comment on column SEL_FUND_PACT_FUNDUSEDETAIL.BUYERCODE
  is '客户编码';
comment on column SEL_FUND_PACT_FUNDUSEDETAIL.SECONDACCOUNT
  is '二级账户名';
comment on column SEL_FUND_PACT_FUNDUSEDETAIL.PACTNO
  is '合同号';
comment on column SEL_FUND_PACT_FUNDUSEDETAIL.ASKPLAN_ID
  is '计划单号';
comment on column SEL_FUND_PACT_FUNDUSEDETAIL.WEIGHT
  is '重量';
comment on column SEL_FUND_PACT_FUNDUSEDETAIL.MONEY
  is '发生金额=ASKPLAN_MONEY +TRANS_MONEY';
comment on column SEL_FUND_PACT_FUNDUSEDETAIL.ASKPLAN_MONEY
  is '计划单号对应的金额';
comment on column SEL_FUND_PACT_FUNDUSEDETAIL.TRANS_MONEY
  is '计划单号对应的运费';
comment on column SEL_FUND_PACT_FUNDUSEDETAIL.TRANS_TIME
  is '发生时间';
comment on column SEL_FUND_PACT_FUNDUSEDETAIL.ISMONEYVALID
  is '0有效，1无效，装车时需要将同计划单号，同合同号的数据设置无效';
comment on column SEL_FUND_PACT_FUNDUSEDETAIL.TRANS_PERSON
  is '操作人';
comment on column SEL_FUND_PACT_FUNDUSEDETAIL.REMARK
  is '备注';
comment on column SEL_FUND_PACT_FUNDUSEDETAIL.UPDATETIME
  is '更新时间';
create index IDX_PACTFUNDUSEDDETAIL on SEL_FUND_PACT_FUNDUSEDETAIL (BALANCESUBJECT, BUYERCODE, SECONDACCOUNT, PACTNO);

prompt
prompt Creating table SEL_FUND_PACT_TRANSDETAIL
prompt ========================================
prompt
create table SEL_FUND_PACT_TRANSDETAIL
(
  TRANS_SEQ      VARCHAR2(40) not null,
  TRANS_TYPE     VARCHAR2(1) not null,
  BALANCESUBJECT VARCHAR2(40),
  BUYERCODE      VARCHAR2(40),
  SECONDACCOUNT  VARCHAR2(100),
  PACTNO         VARCHAR2(100),
  MONEY          NUMBER,
  LEAVEMONEY     NUMBER,
  OTHERACCOUNT   VARCHAR2(100),
  TRANS_TIME     VARCHAR2(20),
  TRANS_PERSON   VARCHAR2(40),
  REMARK         VARCHAR2(400),
  UPDATETIME     VARCHAR2(20)
)
;
comment on table SEL_FUND_PACT_TRANSDETAIL
  is '合同账户资金转账明细表，合同和二级账户中的转账';
comment on column SEL_FUND_PACT_TRANSDETAIL.TRANS_SEQ
  is '序列';
comment on column SEL_FUND_PACT_TRANSDETAIL.TRANS_TYPE
  is '类型0进1出';
comment on column SEL_FUND_PACT_TRANSDETAIL.BALANCESUBJECT
  is '结算主体';
comment on column SEL_FUND_PACT_TRANSDETAIL.BUYERCODE
  is '客户编码';
comment on column SEL_FUND_PACT_TRANSDETAIL.SECONDACCOUNT
  is '二级账户名';
comment on column SEL_FUND_PACT_TRANSDETAIL.MONEY
  is '发生金额';
comment on column SEL_FUND_PACT_TRANSDETAIL.LEAVEMONEY
  is '发生前剩余资金';
comment on column SEL_FUND_PACT_TRANSDETAIL.OTHERACCOUNT
  is '对方账户';
comment on column SEL_FUND_PACT_TRANSDETAIL.TRANS_TIME
  is '发生时间';
comment on column SEL_FUND_PACT_TRANSDETAIL.TRANS_PERSON
  is '操作人';
comment on column SEL_FUND_PACT_TRANSDETAIL.REMARK
  is '备注';
comment on column SEL_FUND_PACT_TRANSDETAIL.UPDATETIME
  is '更新时间';
alter table SEL_FUND_PACT_TRANSDETAIL
  add constraint PK_SEL_FUND_PACT_TRANSDETAIL primary key (TRANS_SEQ, TRANS_TYPE);
create index IDX_PACT_TRANSDETAIL on SEL_FUND_PACT_TRANSDETAIL (BALANCESUBJECT, BUYERCODE, SECONDACCOUNT, PACTNO);

prompt
prompt Creating table SEL_FUND_SECONDLEVEL_ACCOUNT
prompt ===========================================
prompt
create table SEL_FUND_SECONDLEVEL_ACCOUNT
(
  BALANCESUBJECT VARCHAR2(40) not null,
  BUYERCODE      VARCHAR2(40) not null,
  SECONDACCOUNT  VARCHAR2(100) not null,
  TOTALMONEY     NUMBER,
  LEAVEMONEY     NUMBER,
  IMPORTOR       VARCHAR2(40),
  IMPORTTIME     VARCHAR2(20),
  UPDATETIME     VARCHAR2(20)
)
;
comment on column SEL_FUND_SECONDLEVEL_ACCOUNT.BALANCESUBJECT
  is '结算主体';
comment on column SEL_FUND_SECONDLEVEL_ACCOUNT.BUYERCODE
  is '客户编码';
comment on column SEL_FUND_SECONDLEVEL_ACCOUNT.SECONDACCOUNT
  is '二级账户名称';
comment on column SEL_FUND_SECONDLEVEL_ACCOUNT.TOTALMONEY
  is '账户总资金';
comment on column SEL_FUND_SECONDLEVEL_ACCOUNT.LEAVEMONEY
  is '账户剩余资金';
comment on column SEL_FUND_SECONDLEVEL_ACCOUNT.IMPORTOR
  is '录入人';
comment on column SEL_FUND_SECONDLEVEL_ACCOUNT.IMPORTTIME
  is '录入时间';
comment on column SEL_FUND_SECONDLEVEL_ACCOUNT.UPDATETIME
  is '更新时间';
alter table SEL_FUND_SECONDLEVEL_ACCOUNT
  add constraint PK_SEL_FUND_SECONDLEVEL_ACCOUN primary key (BALANCESUBJECT, BUYERCODE, SECONDACCOUNT);

prompt
prompt Creating table SEL_FUND_SECONDLE_USEDETAIL
prompt ==========================================
prompt
create table SEL_FUND_SECONDLE_USEDETAIL
(
  TRANS_SEQ      VARCHAR2(40) not null,
  TRANS_TYPE     VARCHAR2(1) not null,
  BALANCESUBJECT VARCHAR2(40),
  BUYERCODE      VARCHAR2(40),
  SECONDACCOUNT  VARCHAR2(100),
  MONEY          NUMBER,
  LEAVEMONEY     NUMBER,
  OTHERACCOUNT   VARCHAR2(100),
  TRANS_TIME     VARCHAR2(20),
  TRANS_PERSON   VARCHAR2(40),
  REMARK         VARCHAR2(400),
  UPDATETIME     VARCHAR2(20)
)
;
comment on column SEL_FUND_SECONDLE_USEDETAIL.TRANS_SEQ
  is '序列';
comment on column SEL_FUND_SECONDLE_USEDETAIL.TRANS_TYPE
  is '类型0进1出';
comment on column SEL_FUND_SECONDLE_USEDETAIL.BALANCESUBJECT
  is '结算主体';
comment on column SEL_FUND_SECONDLE_USEDETAIL.BUYERCODE
  is '客户编码';
comment on column SEL_FUND_SECONDLE_USEDETAIL.SECONDACCOUNT
  is '二级账户名';
comment on column SEL_FUND_SECONDLE_USEDETAIL.MONEY
  is '发生金额';
comment on column SEL_FUND_SECONDLE_USEDETAIL.LEAVEMONEY
  is '发生前剩余资金';
comment on column SEL_FUND_SECONDLE_USEDETAIL.OTHERACCOUNT
  is '对方账户';
comment on column SEL_FUND_SECONDLE_USEDETAIL.TRANS_TIME
  is '发生时间';
comment on column SEL_FUND_SECONDLE_USEDETAIL.TRANS_PERSON
  is '操作人';
comment on column SEL_FUND_SECONDLE_USEDETAIL.REMARK
  is '备注';
comment on column SEL_FUND_SECONDLE_USEDETAIL.UPDATETIME
  is '更新时间';
alter table SEL_FUND_SECONDLE_USEDETAIL
  add constraint PK_SEL_FUND_SECONDLE_USEDETAIL primary key (TRANS_SEQ, TRANS_TYPE);
create index IDX_SECONDLE_USEDDETAIL on SEL_FUND_SECONDLE_USEDETAIL (BALANCESUBJECT, BUYERCODE, SECONDACCOUNT);

prompt
prompt Creating table SEL_FUND_USERANDBALRELATION
prompt ==========================================
prompt
create table SEL_FUND_USERANDBALRELATION
(
  USERID         VARCHAR2(40) not null,
  BALANCESUBJECT VARCHAR2(100),
  IMPORTOR       VARCHAR2(60),
  IMPORTTIME     VARCHAR2(60)
)
;
comment on table SEL_FUND_USERANDBALRELATION
  is '登陆用户和结算单位关系对应表';
comment on column SEL_FUND_USERANDBALRELATION.USERID
  is '登陆用户id';
comment on column SEL_FUND_USERANDBALRELATION.BALANCESUBJECT
  is '结算单位';
comment on column SEL_FUND_USERANDBALRELATION.IMPORTOR
  is '录入人';
comment on column SEL_FUND_USERANDBALRELATION.IMPORTTIME
  is '录入时间';
alter table SEL_FUND_USERANDBALRELATION
  add constraint PRI_USERID primary key (USERID);


spool off