535 lines
14 KiB
SQL
535 lines
14 KiB
SQL
-- Create table 销售订单主表
|
||
create table SA_ORDER
|
||
(
|
||
pk_id NUMBER(20) not null,
|
||
order_code VARCHAR2(30) not null,
|
||
order_type number(6) not null,
|
||
pk_rate NUMBER(6) not null,
|
||
order_amount NUMBER(17,6) default 0 not null,
|
||
order_achieve NUMBER(17,6) default 0 not null,
|
||
postage NUMBER(15,6) default 0 not null,
|
||
member_code VARCHAR2(20) not null,
|
||
rec_name VARCHAR2(100) not null,
|
||
rec_phone VARCHAR2(20) not null,
|
||
rec_province NUMBER(6) not null,
|
||
rec_city NUMBER(6) not null,
|
||
rec_county NUMBER(6) not null,
|
||
rec_address VARCHAR2(200) not null,
|
||
pay_time DATE,
|
||
remark VARCHAR2(200),
|
||
reference VARCHAR2(20),
|
||
placement VARCHAR2(20),
|
||
delivery_way NUMBER(2),
|
||
order_status NUMBER(2),
|
||
vertex VARCHAR2(20) not null,
|
||
del_flag NUMBER(1) default 0 not null,
|
||
pkCreator VARCHAR2(50) not null,
|
||
creation_time DATE default sysdate not null,
|
||
pkModified VARCHAR2(50),
|
||
modified_time DATE,
|
||
pk_country NUMBER(6) not null
|
||
)
|
||
tablespace CLOUD
|
||
pctfree 10
|
||
initrans 1
|
||
maxtrans 255
|
||
storage
|
||
(
|
||
initial 64K
|
||
next 1M
|
||
minextents 1
|
||
maxextents unlimited
|
||
pctincrease 0
|
||
);
|
||
-- Add comments to the columns
|
||
comment on column SA_ORDER.pk_id
|
||
is '主键';
|
||
comment on column SA_ORDER.order_code
|
||
is '订单编号';
|
||
comment on column SA_ORDER.order_type
|
||
is '订单类型 来源于枚举：所属专区';
|
||
comment on column SA_ORDER.pk_rate
|
||
is '汇率 来源于汇率';
|
||
comment on column SA_ORDER.order_amount
|
||
is '订单金额';
|
||
comment on column SA_ORDER.order_achieve
|
||
is '订单业绩';
|
||
comment on column SA_ORDER.postage
|
||
is '邮费';
|
||
comment on column SA_ORDER.member_code
|
||
is '会员编号';
|
||
comment on column SA_ORDER.rec_name
|
||
is '收货人';
|
||
comment on column SA_ORDER.rec_phone
|
||
is '收货电话';
|
||
comment on column SA_ORDER.rec_province
|
||
is '省';
|
||
comment on column SA_ORDER.rec_city
|
||
is '市';
|
||
comment on column SA_ORDER.rec_county
|
||
is '县';
|
||
comment on column SA_ORDER.rec_address
|
||
is '收货地址';
|
||
comment on column SA_ORDER.pay_time
|
||
is '支付时间';
|
||
comment on column SA_ORDER.remark
|
||
is '备注';
|
||
comment on column SA_ORDER.reference
|
||
is '推荐人编号';
|
||
comment on column SA_ORDER.placement
|
||
is '安置人编号';
|
||
comment on column SA_ORDER.delivery_way
|
||
is '发货方式 (1=快递,2=公司自提,3=店铺自提,4=店铺配送)';
|
||
comment on column SA_ORDER.order_status
|
||
is '订单状态 (0=待支付,1=已支付,2=已发货,3=部分发货)';
|
||
comment on column SA_ORDER.vertex
|
||
is '顶点,用于统计六合';
|
||
comment on column SA_ORDER.del_flag
|
||
is '逻辑删除 (0=未删除,1已删除)';
|
||
comment on column SA_ORDER.pkCreator
|
||
is '创建人';
|
||
comment on column SA_ORDER.creation_time
|
||
is '创建时间';
|
||
comment on column SA_ORDER.pkModified
|
||
is '修改人';
|
||
comment on column SA_ORDER.modified_time
|
||
is '修改时间';
|
||
comment on column SA_ORDER.pk_country
|
||
is '国家';
|
||
-- Create/Recreate indexes
|
||
create unique index SA_ORDER_ORDER_CODE on SA_ORDER (ORDER_CODE)
|
||
tablespace CLOUD
|
||
pctfree 10
|
||
initrans 2
|
||
maxtrans 255
|
||
storage
|
||
(
|
||
initial 64K
|
||
next 1M
|
||
minextents 1
|
||
maxextents unlimited
|
||
pctincrease 0
|
||
);
|
||
-- Create/Recreate primary, unique and foreign key constraints
|
||
alter table SA_ORDER
|
||
add constraint SA_ORDER_PK primary key (PK_ID)
|
||
using index
|
||
tablespace CLOUD
|
||
pctfree 10
|
||
initrans 2
|
||
maxtrans 255
|
||
storage
|
||
(
|
||
initial 64K
|
||
next 1M
|
||
minextents 1
|
||
maxextents unlimited
|
||
pctincrease 0
|
||
);
|
||
|
||
|
||
create sequence SA_ORDER_SEQ
|
||
minvalue 1
|
||
maxvalue 999999999999999999999999
|
||
start with 1
|
||
increment by 1
|
||
cache 20;
|
||
|
||
|
||
-- Create table 销售订单明细表
|
||
create table SA_ORDER_ITEMS
|
||
(
|
||
pk_id NUMBER(20) not null,
|
||
pk_order NUMBER(20) not null,
|
||
pk_product NUMBER(6) not null,
|
||
pk_wares NUMBER(6) not null,
|
||
pk_specs NUMBER(6) not null,
|
||
price NUMBER(17,6) default 0 not null,
|
||
pk_rate NUMBER(6) not null,
|
||
achievement NUMBER(17,6) default 0 not null,
|
||
quantity NUMBER(10) default 0 not null,
|
||
pk_grade NUMBER(6) default 0 not null,
|
||
pk_awards NUMBER(6) default 0 not null,
|
||
order_status NUMBER(2) default 0 not null,
|
||
del_flag NUMBER(1) default 0 not null,
|
||
pkCreator VARCHAR2(50) not null,
|
||
creation_time DATE default sysdate not null,
|
||
pkModified VARCHAR2(50),
|
||
modified_time DATE,
|
||
pk_country NUMBER(6) not null
|
||
)
|
||
tablespace CLOUD
|
||
pctfree 10
|
||
initrans 1
|
||
maxtrans 255
|
||
storage
|
||
(
|
||
initial 64K
|
||
next 1M
|
||
minextents 1
|
||
maxextents unlimited
|
||
pctincrease 0
|
||
);
|
||
-- Add comments to the table
|
||
comment on table SA_ORDER_ITEMS
|
||
is '销售订单-明细表';
|
||
-- Add comments to the columns
|
||
comment on column SA_ORDER_ITEMS.pk_id
|
||
is '主键';
|
||
comment on column SA_ORDER_ITEMS.pk_order
|
||
is '销售订单主表';
|
||
comment on column SA_ORDER_ITEMS.pk_product
|
||
is '产品主键';
|
||
comment on column SA_ORDER_ITEMS.pk_wares
|
||
is '商品主键';
|
||
comment on column SA_ORDER_ITEMS.pk_specs
|
||
is '规格主键';
|
||
comment on column SA_ORDER_ITEMS.price
|
||
is '价格';
|
||
comment on column SA_ORDER_ITEMS.pk_rate
|
||
is '汇率';
|
||
comment on column SA_ORDER_ITEMS.achievement
|
||
is '业绩';
|
||
comment on column SA_ORDER_ITEMS.quantity
|
||
is '数量';
|
||
comment on column SA_ORDER_ITEMS.pk_grade
|
||
is '等级';
|
||
comment on column SA_ORDER_ITEMS.pk_awards
|
||
is '奖衔';
|
||
comment on column SA_ORDER_ITEMS.order_status
|
||
is '订单状态 (0=待支付,1=已支付,2=已发货,3=部分发货)';
|
||
comment on column SA_ORDER_ITEMS.del_flag
|
||
is '逻辑删除 (0=未删除,1已删除)';
|
||
comment on column SA_ORDER_ITEMS.pkCreator
|
||
is '创建人';
|
||
comment on column SA_ORDER_ITEMS.creation_time
|
||
is '创建时间';
|
||
comment on column SA_ORDER_ITEMS.pkModified
|
||
is '修改人';
|
||
comment on column SA_ORDER_ITEMS.modified_time
|
||
is '修改时间';
|
||
comment on column SA_ORDER_ITEMS.pk_country
|
||
is '国家';
|
||
-- Create/Recreate primary, unique and foreign key constraints
|
||
alter table SA_ORDER_ITEMS
|
||
add constraint SA_ORDER_ITEMS_PK primary key (PK_ID)
|
||
using index
|
||
tablespace CLOUD
|
||
pctfree 10
|
||
initrans 2
|
||
maxtrans 255
|
||
storage
|
||
(
|
||
initial 64K
|
||
next 1M
|
||
minextents 1
|
||
maxextents unlimited
|
||
pctincrease 0
|
||
);
|
||
|
||
|
||
|
||
create sequence SA_ORDER_ITEMS_SEQ
|
||
minvalue 1
|
||
maxvalue 999999999999999999999999
|
||
start with 1
|
||
increment by 1
|
||
cache 20;
|
||
|
||
|
||
-- Create table
|
||
create table SA_DELIVER
|
||
(
|
||
pk_id NUMBER(20) not null,
|
||
deliver_code VARCHAR2(50) not null,
|
||
logistics_code VARCHAR2(50),
|
||
logistics_company NUMBER(2),
|
||
rec_name VARCHAR2(100) not null,
|
||
rec_phone VARCHAR2(20) not null,
|
||
rec_province NUMBER(6) not null,
|
||
rec_city NUMBER(6) not null,
|
||
rec_county NUMBER(6) not null,
|
||
deliver_status NUMBER(2) not null,
|
||
deliver_time DATE,
|
||
del_flag NUMBER(1) default 0 not null,
|
||
pkCreator VARCHAR2(50) not null,
|
||
creation_time DATE default sysdate not null,
|
||
pkModified VARCHAR2(50),
|
||
modified_time DATE,
|
||
pk_country NUMBER(6) not null
|
||
)
|
||
tablespace CLOUD
|
||
pctfree 10
|
||
initrans 1
|
||
maxtrans 255
|
||
storage
|
||
(
|
||
initial 64K
|
||
next 1M
|
||
minextents 1
|
||
maxextents unlimited
|
||
pctincrease 0
|
||
);
|
||
-- Add comments to the table
|
||
comment on table SA_DELIVER
|
||
is '销售订单-发货单';
|
||
-- Add comments to the columns
|
||
comment on column SA_DELIVER.pk_id
|
||
is '主键';
|
||
comment on column SA_DELIVER.deliver_code
|
||
is '发货单号';
|
||
comment on column SA_DELIVER.logistics_code
|
||
is '物流单号';
|
||
comment on column SA_DELIVER.logistics_company
|
||
is '物流公司';
|
||
comment on column SA_DELIVER.rec_name
|
||
is '收货姓名';
|
||
comment on column SA_DELIVER.rec_phone
|
||
is '收货电话';
|
||
comment on column SA_DELIVER.rec_province
|
||
is '收货省';
|
||
comment on column SA_DELIVER.rec_city
|
||
is '收货市';
|
||
comment on column SA_DELIVER.rec_county
|
||
is '收货县';
|
||
comment on column SA_DELIVER.deliver_status
|
||
is '发货状态(1=未发货,2=已发货,3=部分发货,4=已收货，5=已退货)';
|
||
comment on column SA_DELIVER.deliver_time
|
||
is '发货时间';
|
||
comment on column SA_DELIVER.del_flag
|
||
is '逻辑删除 (0=未删除,1已删除)';
|
||
comment on column SA_DELIVER.pkCreator
|
||
is '创建人';
|
||
comment on column SA_DELIVER.creation_time
|
||
is '创建时间';
|
||
comment on column SA_DELIVER.pkModified
|
||
is '修改人';
|
||
comment on column SA_DELIVER.modified_time
|
||
is '修改时间';
|
||
comment on column SA_DELIVER.pk_country
|
||
is '国家';
|
||
-- Create/Recreate indexes
|
||
create unique index SA_DELIVER_DELIVER_CODE on SA_DELIVER (DELIVER_CODE)
|
||
tablespace CLOUD
|
||
pctfree 10
|
||
initrans 2
|
||
maxtrans 255
|
||
storage
|
||
(
|
||
initial 64K
|
||
next 1M
|
||
minextents 1
|
||
maxextents unlimited
|
||
pctincrease 0
|
||
);
|
||
-- Create/Recreate primary, unique and foreign key constraints
|
||
alter table SA_DELIVER
|
||
add constraint SA_DELIVER_PK primary key (PK_ID)
|
||
using index
|
||
tablespace CLOUD
|
||
pctfree 10
|
||
initrans 2
|
||
maxtrans 255
|
||
storage
|
||
(
|
||
initial 64K
|
||
next 1M
|
||
minextents 1
|
||
maxextents unlimited
|
||
pctincrease 0
|
||
);
|
||
|
||
|
||
-- Create table 发货单
|
||
create table SA_DELIVER
|
||
(
|
||
pk_id NUMBER(20) not null,
|
||
deliver_code VARCHAR2(50) not null,
|
||
logistics_code VARCHAR2(50),
|
||
logistics_company NUMBER(2),
|
||
rec_name VARCHAR2(100) not null,
|
||
rec_phone VARCHAR2(20) not null,
|
||
rec_province NUMBER(6) not null,
|
||
rec_city NUMBER(6) not null,
|
||
rec_county NUMBER(6) not null,
|
||
deliver_status NUMBER(2) not null,
|
||
deliver_time DATE,
|
||
del_flag NUMBER(1) default 0 not null,
|
||
pkCreator VARCHAR2(50) not null,
|
||
creation_time DATE default sysdate not null,
|
||
pkModified VARCHAR2(50),
|
||
modified_time DATE,
|
||
pk_country NUMBER(6) not null
|
||
)
|
||
tablespace CLOUD
|
||
pctfree 10
|
||
initrans 1
|
||
maxtrans 255
|
||
storage
|
||
(
|
||
initial 64K
|
||
next 1M
|
||
minextents 1
|
||
maxextents unlimited
|
||
pctincrease 0
|
||
);
|
||
-- Add comments to the table
|
||
comment on table SA_DELIVER
|
||
is '发货单';
|
||
-- Add comments to the columns
|
||
comment on column SA_DELIVER.pk_id
|
||
is '主键';
|
||
comment on column SA_DELIVER.deliver_code
|
||
is '发货单号';
|
||
comment on column SA_DELIVER.logistics_code
|
||
is '物流单号';
|
||
comment on column SA_DELIVER.logistics_company
|
||
is '物流公司';
|
||
comment on column SA_DELIVER.rec_name
|
||
is '收货姓名';
|
||
comment on column SA_DELIVER.rec_phone
|
||
is '收货电话';
|
||
comment on column SA_DELIVER.rec_province
|
||
is '收货省';
|
||
comment on column SA_DELIVER.rec_city
|
||
is '收货市';
|
||
comment on column SA_DELIVER.rec_county
|
||
is '收货县';
|
||
comment on column SA_DELIVER.deliver_status
|
||
is '发货状态(1=未发货,2=已发货,3=部分发货,4=已收货，5=已退货)';
|
||
comment on column SA_DELIVER.deliver_time
|
||
is '发货时间';
|
||
comment on column SA_DELIVER.del_flag
|
||
is '逻辑删除 (0=未删除,1已删除)';
|
||
comment on column SA_DELIVER.pkCreator
|
||
is '创建人';
|
||
comment on column SA_DELIVER.creation_time
|
||
is '创建时间';
|
||
comment on column SA_DELIVER.pkModified
|
||
is '修改人';
|
||
comment on column SA_DELIVER.modified_time
|
||
is '修改时间';
|
||
comment on column SA_DELIVER.pk_country
|
||
is '国家';
|
||
-- Create/Recreate indexes
|
||
create unique index SA_DELIVER_DELIVER_CODE on SA_DELIVER (DELIVER_CODE)
|
||
tablespace CLOUD
|
||
pctfree 10
|
||
initrans 2
|
||
maxtrans 255
|
||
storage
|
||
(
|
||
initial 64K
|
||
next 1M
|
||
minextents 1
|
||
maxextents unlimited
|
||
pctincrease 0
|
||
);
|
||
-- Create/Recreate primary, unique and foreign key constraints
|
||
alter table SA_DELIVER
|
||
add constraint SA_DELIVER_PK primary key (PK_ID)
|
||
using index
|
||
tablespace CLOUD
|
||
pctfree 10
|
||
initrans 2
|
||
maxtrans 255
|
||
storage
|
||
(
|
||
initial 64K
|
||
next 1M
|
||
minextents 1
|
||
maxextents unlimited
|
||
pctincrease 0
|
||
);
|
||
|
||
|
||
create sequence SA_DELIVER_SEQ
|
||
minvalue 1
|
||
maxvalue 999999999999999999999999
|
||
start with 1
|
||
increment by 1
|
||
cache 20;
|
||
|
||
-- Create table 发货单明细表
|
||
create table SA_DELIVER_ITEMS
|
||
(
|
||
pk_id NUMBER(20) not null,
|
||
pk_order NUMBER(20) not null,
|
||
pk_product NUMBER(20) not null,
|
||
pk_specs NUMBER(6) not null,
|
||
pk_storehouse NUMBER(6) not null,
|
||
quantity NUMBER(10) default 0 not null,
|
||
del_flag NUMBER(1) default 0 not null,
|
||
pkCreator VARCHAR2(50) not null,
|
||
creation_time DATE default sysdate not null,
|
||
pkModified VARCHAR2(50),
|
||
modified_time DATE,
|
||
pk_country NUMBER(6) not null
|
||
)
|
||
tablespace CLOUD
|
||
pctfree 10
|
||
initrans 1
|
||
maxtrans 255
|
||
storage
|
||
(
|
||
initial 64K
|
||
next 1M
|
||
minextents 1
|
||
maxextents unlimited
|
||
pctincrease 0
|
||
);
|
||
-- Add comments to the table
|
||
comment on table SA_DELIVER_ITEMS
|
||
is '发货单明细表';
|
||
-- Add comments to the columns
|
||
comment on column SA_DELIVER_ITEMS.pk_id
|
||
is '主键';
|
||
comment on column SA_DELIVER_ITEMS.pk_order
|
||
is '订单主键';
|
||
comment on column SA_DELIVER_ITEMS.pk_product
|
||
is '产品主键';
|
||
comment on column SA_DELIVER_ITEMS.pk_specs
|
||
is '产品规格主键';
|
||
comment on column SA_DELIVER_ITEMS.pk_storehouse
|
||
is '仓库';
|
||
comment on column SA_DELIVER_ITEMS.quantity
|
||
is '产品数量';
|
||
comment on column SA_DELIVER_ITEMS.del_flag
|
||
is '逻辑删除 (0=未删除,1已删除)';
|
||
comment on column SA_DELIVER_ITEMS.pkCreator
|
||
is '创建人';
|
||
comment on column SA_DELIVER_ITEMS.creation_time
|
||
is '创建时间';
|
||
comment on column SA_DELIVER_ITEMS.pkModified
|
||
is '修改人';
|
||
comment on column SA_DELIVER_ITEMS.modified_time
|
||
is '修改时间';
|
||
comment on column SA_DELIVER_ITEMS.pk_country
|
||
is '国家';
|
||
-- Create/Recreate primary, unique and foreign key constraints
|
||
alter table SA_DELIVER_ITEMS
|
||
add constraint SA_DELIVER_ITEMS_PK primary key (PK_ID)
|
||
using index
|
||
tablespace CLOUD
|
||
pctfree 10
|
||
initrans 2
|
||
maxtrans 255
|
||
storage
|
||
(
|
||
initial 64K
|
||
next 1M
|
||
minextents 1
|
||
maxextents unlimited
|
||
pctincrease 0
|
||
);
|
||
|
||
|
||
|
||
|
||
create sequence SA_DELIVER_ITEMS_ID_SEQ
|
||
minvalue 1
|
||
maxvalue 999999999999999999999999
|
||
start with 1
|
||
increment by 1
|
||
cache 20;
|