rain.rar 减压到d:在oracle 建TRAIN
imp train/train@orcl file =d:\train.dmp
buffer = 4096000 full = y commit = y
ignore =y feedback =10000 log = d:\imp.log
create user train identified by train
DEFAULT TABLESPACE USERS ;
grant dba to train
drop user gykg10 cascade;
create user gykg10 identified by gykg10
DEFAULT TABLESPACE USERS ;
grant dba to gykg10
create table bms_t_dye
(inputmanid number(10,0),
companyid number(10,0),
credate date ,
primary key (inputmanid ,companyid ))
CREATE VIEW bms_t_dye_v AS
SELECT
A.COMPANYID, B.COMPANYNAME,
A.INPUTMANID, C.EMPLOYEENAME,
A.CREDATE
FROM
BMS_T_dye A,PUB_COMPANY B,
PUB_EMPLOYEE C
WHERE
(A.COMPANYID=B.COMPANYID(+))and (A.INPUTMANID=C.EMPLOYEEID(+))
insert into bms
create table train_pub_goods_dye
(Goodsid number(10,0),
goodsopcode varchar2(10),
goodsname varchar2(40),
goodspinyin varchar2(20),
goodstype varchar2(20),
Prodarea varchar2(20),
goodsinvname varchar2(80),
goodsunit varchar2(20),
goodsno varchar2(20),
Validperiod number(10),
Periodunit varchar2(2),
Usestatus varchar2(10),
Credate date,
supplytaxrate number(10,6),
salestaxrate number(10,6),
Inputmanid number(10),
Memo varchar2(200),
constraint train_pub_goods_dye_pk primary key (goodsid));
create or replace view train_pub_goods_dye_v as
select
a.goodsid ,
a.goodsopcode,
a.goodsname,
a.goodspinyin,
a.goodstype,
a.prodarea,
a.goodsinvname,
a.goodsunit,
a.goodsno,
a.validperiod,
b.opcode,
b.employeename
from
train_pub_goods_dye a,pub_employee b
where
( a.inputmanid = b.employeeid(+))
create table bms_su_doc_dye
(
Sudocid Number(10,0) not null,-- 总单内部标识号 主键列(采用内部序列号自动增加初值)
Credate Date ,--填单日期, 初值为系统当前日期
Supplyid Number(10,0),-- 供应商标识号 选自供应商HOV;关联表供应商表:pub_supplyer
Total Number(12,2),-- 合计金额 细目金额总和(自动计算)
Dtl_lines Number(5,0),-- 细目笔数 所存在的细目数(自动计算)
Inputmanid Number(10,0),-- 录入员标识号 初值:当前操作员关联表人员表:pub_employee
Arrivedate Date,-- 收货日期 初值为系统当前日期,可修改
Deliverdate Date,-- 发货日期 初值为系统当前日期,可修改
Memo Varchar(200),-- 备注
Entryid Number(10,0),-- 独立核算单元标识号 选自独立核算单元HOV关联表独立核算单元表:pub_entry
Initflag Number(1,0),-- 初始标志 0 临时 1正式;初值为临时。
CONSTRAINT bms_sudoc_dye_pk PRIMARY KEY (sudocid)
);
create or replace view bms_su_doc_dye_v as
select
a.Sudocid,
a.Credate,
a.Supplyid,
b.supplyname,
a.total,
a.dtl_lines,
a.inputmanid,
c.employeename inputman,
a.Arrivedate,
a.Deliverdate,
a.Memo,
a.Entryid,
d.entryname,
a.Initflag
from
bms_su_doc_dye a,
pub_supplyer b,
pub_employee c,
pub_entry d
where
a.supplyid = b.supplyid(+) and
a.inputmanid = c.employeeid(+) and
a.entryid = d.entryid(+)
;
create table bms_su_dtl_dye
(
Sudocid Number(10,0) not null,--进货单总单标识号 外键,关联进货总单表根据总单ID自动填写
Sudocdtlid Number(10,0),-- 进货单细目标识号 主键(采用内部序列号自动增加初值)
Supplyerid Number(10,0),-- 业务员标识号 选自HOV关联表:人员表:pub_employee
Deptid Number(10,0),-- 业务员部门标识号 来自人员表;选自HOV
Goodsid Number(10,0),-- 货品标识号 选自HOV;关联表:货品表:pub_goods
Goodsdtlid Number(10,0),--货品明细标识号 选自HOV关联表:货品表:pub_goodsdetail
Storageid Number(10,0),-- 保管帐标识号 选自HOV关联表:保管帐表:bms_st_def
Goodsunit Varchar(10),-- 货品基本单位 选自HOV
Goodsqty Number(16,6),-- 商品数量 (基本单位数量)
Unitprice Number(20,10),-- 单价
Total_line Number(12,2),-- 金额 单价 * 数量
Paymode Number(2,0),-- 承付模式, 选自HOV由Bms_su_paymode_def定义
Usestatus Number(2,0),-- 单据废除标志0:停用;1:使用;2:完成系统字典:su_usetatus初值为 1
Memo Varchar2(200),-- 备注
CONSTRAINT bms_sudtl_dye_pk PRIMARY KEY (Sudocdtlid),
CONSTRAINT bms_sudtl_dye_fk FOREIGN KEY (sudocid) REFERENCES bms_su_doc_dye(sudocid)
);
create or replace view bms_su_dtl_dye_v as
select
a.Sudocid,
a.Sudocdtlid,
a.Supplyerid,
b.employeename supplyername,
a.Deptid,
c.companyname deptname,
a.Goodsid,
d.goodsname,
d.goodstype,
d.prodarea,
a.Goodsdtlid,
e.packname,
a.Storageid,
f.opcode storageopcode,
f.storagename,
a.Goodsunit,
a.Goodsqty,
a.Unitprice,
a.Total_line,
a.Paymode,
a.Usestatus,
a.Memo
from
bms_su_dtl_dye a,
pub_employee b,
pub_company c,
pub_goods d,
pub_goods_detail e,
bms_st_def f
where
a.Supplyerid = b.employeeid(+) and
a.Deptid = c.companyid(+) and
a.Goodsid = d.goodsid(+) and
a.Goodsdtlid = e.goodsdtlid(+) and
a.Storageid = f.storageid(+)