EKDScode/shop_db.sql

create table AR_USER
(
	ID VARCHAR2(32) default SYS_GUID() not null
		primary key,
	USER_NAME VARCHAR2(16) not null,
	PASSWORD_HASH VARCHAR2(255) not null,
	PAY_PASSWORD VARCHAR2(255) not null,
	NATION NUMBER(2) default 0 not null,
	REAL_NAME VARCHAR2(128) not null,
	ID_CARD VARCHAR2(18) not null,
	ID_TYPE NUMBER(1) default 0 not null,
	MOBILE VARCHAR2(11) not null,
	ADDRESS VARCHAR2(255) not null,
	ID_IMAGE VARCHAR2(255) default '',
	OPEN_BANK VARCHAR2(32) not null,
	BANK_ADDRESS VARCHAR2(255) not null,
	BANK_NO VARCHAR2(32) not null,
	BANK_PROVINCE NUMBER(6) default 0 not null,
	BANK_CITY NUMBER(6) default 0 not null,
	BANK_COUNTY NUMBER(6) default 0 not null,
	SPOUSE_NAME VARCHAR2(16) default '',
	SPOUSE_IDCARD VARCHAR2(18) default '',
	CREATED_AT NUMBER(10) default 0 not null,
	UPDATED_AT NUMBER(10) default 0 not null,
	STATUS NUMBER(1) default 1 not null,
	DEC_CLOSED NUMBER(1) default 0 not null,
	DEC_CLOSED_AT NUMBER(10) default 0 not null,
	DEC_LV VARCHAR2(32) default '',
	EMP_LV VARCHAR2(32) default '',
	IS_STORE NUMBER(1) default 0 not null,
	STORE_ID VARCHAR2(32) default '',
	PROVINCE NUMBER(6) default 0 not null,
	CITY NUMBER(6) default 0 not null,
	COUNTY NUMBER(6) default 0 not null,
	TEL VARCHAR2(16) default '',
	LOGIN_TIMES NUMBER(10) default 0 not null,
	LOGIN_IP VARCHAR2(15) default '',
	LOGIN_AT NUMBER(10) default 0 not null,
	REG_IS_COMPLETED NUMBER(1) default 0 not null,
	COMPLETED_AT NUMBER(10) default 0 not null,
	PULL_MESSAGE_AT NUMBER(10) default 0 not null,
	SUB_COM_ID VARCHAR2(32) default '',
	AVATAR VARCHAR2(255) not null,
	DELETED NUMBER(1) default 0 not null,
	DELETED_AT NUMBER(10) default 0 not null,
	ALLOW_TRANSFER NUMBER(1) default 1 not null,
	TRANSFER_PROP NUMBER(10,2) default 0.00 not null,
	PARTITION_DATE DATE default SYSDATE not null
)
/

comment on table AR_USER is '用户'
/

comment on column AR_USER.USER_NAME is '帐号'
/

comment on column AR_USER.PASSWORD_HASH is '登录密码'
/

comment on column AR_USER.PAY_PASSWORD is '支付密码'
/

comment on column AR_USER.NATION is '民族'
/

comment on column AR_USER.REAL_NAME is '姓名'
/

comment on column AR_USER.ID_CARD is '身份证号'
/

comment on column AR_USER.ID_TYPE is '证件类型'
/

comment on column AR_USER.MOBILE is '手机号'
/

comment on column AR_USER.ADDRESS is '联系地址'
/

comment on column AR_USER.ID_IMAGE is '证件图片'
/

comment on column AR_USER.OPEN_BANK is '开户行'
/

comment on column AR_USER.BANK_ADDRESS is '银行地址'
/

comment on column AR_USER.BANK_NO is '银行卡号'
/

comment on column AR_USER.BANK_PROVINCE is '银行省份'
/

comment on column AR_USER.BANK_CITY is '银行城市'
/

comment on column AR_USER.BANK_COUNTY is '银行县区'
/

comment on column AR_USER.SPOUSE_NAME is '配偶姓名'
/

comment on column AR_USER.SPOUSE_IDCARD is '配偶证件号'
/

comment on column AR_USER.CREATED_AT is '创建时间'
/

comment on column AR_USER.UPDATED_AT is '更新时间'
/

comment on column AR_USER.STATUS is '状态'
/

comment on column AR_USER.DEC_CLOSED is '是否关闭报单功能'
/

comment on column AR_USER.DEC_CLOSED_AT is '关闭时间'
/

comment on column AR_USER.DEC_LV is '报单级别'
/

comment on column AR_USER.EMP_LV is '聘级'
/

comment on column AR_USER.IS_STORE is '是否为专卖店'
/

comment on column AR_USER.STORE_ID is '专卖店ID'
/

comment on column AR_USER.PROVINCE is '省份'
/

comment on column AR_USER.CITY is '城市'
/

comment on column AR_USER.COUNTY is '县区'
/

comment on column AR_USER.TEL is '座机'
/

comment on column AR_USER.LOGIN_TIMES is '登录次数'
/

comment on column AR_USER.LOGIN_IP is '登录IP'
/

comment on column AR_USER.LOGIN_AT is '登录时间'
/

comment on column AR_USER.REG_IS_COMPLETED is '注册完成'
/

comment on column AR_USER.COMPLETED_AT is '注册完成时间'
/

comment on column AR_USER.PULL_MESSAGE_AT is '拉取消息的时间'
/

comment on column AR_USER.SUB_COM_ID is '子公司ID'
/

comment on column AR_USER.AVATAR is '头像'
/

comment on column AR_USER.DELETED is '是否删除'
/

comment on column AR_USER.DELETED_AT is '删除时间'
/

comment on column AR_USER.ALLOW_TRANSFER is '开启转帐功能'
/

comment on column AR_USER.TRANSFER_PROP is '转帐比例'
/

comment on column AR_USER.PARTITION_DATE is '分区时间'
/

create unique index AR_AR_USER_NAME_UIN
	on AR_USER (USER_NAME)
/



create table AR_ORDER
(
	SN VARCHAR2(20) not null
		primary key,
	ORI_SN NUMBER(20) not null,
	USER_ID VARCHAR2(32) not null,
	USER_NAME VARCHAR2(16) not null,
	REAL_NAME VARCHAR2(16) not null,
	USER_DEC_LV VARCHAR2(32) not null,
	NEW_USER_ID VARCHAR2(32) default '',
	STATUS NUMBER(3) default 0 not null,
	DELIVERY_ID VARCHAR2(32) default '',
	FREIGHT NUMBER(10,2) default 0.00 not null,
	FREE_FREIGHT NUMBER(10,2) default 0.00 not null,
	CONSIGNEE VARCHAR2(16) default '',
	MOBILE VARCHAR2(11) default '',
	TEL VARCHAR2(16) default '',
	PROVINCE NUMBER(6) default 0 not null,
	CITY NUMBER(6) default 0 not null,
	COUNTY NUMBER(6) default 0 not null,
	ADDRESS VARCHAR2(255) default '' not null,
	ORDER_AMOUNT NUMBER(16,2) default 0.00 not null,
	PAY_AMOUNT NUMBER(16,2) default 0.00 not null,
	PV NUMBER(16,2) default 0.00 not null,
	PAY_PV NUMBER(16,2) default 0.00 not null,
	CREATED_AT NUMBER(10) default 0 not null,
	PAY_AT NUMBER(10) default 0 not null,
	GRABED NUMBER(1) default 0 not null,
	GRAB_AT NUMBER(10) default 0 not null,
	EXPRESS_ID NUMBER(2) default 0 not null,
	TRACK_NO VARCHAR2(32) default '',
	SPLIT_NUMS NUMBER(10) default 0 not null,
	SPLIT_AT NUMBER(10) default 0 not null,
	REMARK VARCHAR2(255) default '',
	REBATE NUMBER(16,2) default 0.000 not null,
	ORDER_TYPE VARCHAR2(2) not null,
	WALLET_TYPE VARCHAR2(32) not null,
	MERGE_SN NUMBER(20) default 0 not null,
	MERGE_AT NUMBER(10) default 0 not null,
	EXCHANGE_DIFF_AMOUNT NUMBER(16,2) default 0.000 not null,
	ALLOW_EXCHANGE NUMBER(1) default 1 not null,
	EXCHANGE_AT NUMBER(10) default 0 not null,
	ORDER_MAIN VARCHAR2(16) not null,
	PERIOD_AT NUMBER(10) default 0 not null,
	DELIVERY_PERIOD NUMBER(10) default 0 not null,
	DELIVERY_AT NUMBER(10) default 0 not null,
	DELIVERY_SUB_COM VARCHAR2(32) default '',
	DELIVERY_STATUS NUMBER(2) default 0 not null,
	UPDATED_AT NUMBER(10) default 0 not null,
	UPDATER VARCHAR2(16) default '',
	MAIN_SUB_COM VARCHAR2(32) default '',
	FLOW_TO_USER_ID VARCHAR2(32) default '',
	FLOW_TO_USER_LV VARCHAR2(32) default '',
	CREATER VARCHAR2(16) not null,
	IS_FIRST_PAY NUMBER(1) default 1 not null,
	DATA_AMOUNT NUMBER(10,2) default 0.00 not null,
	IS_TAKE_EFFECT NUMBER(1) default 0 not null,
	PARTITION_DATE DATE default SYSDATE not null,
	TEMP_DATA VARCHAR2(255) default '',
	TAKE_EFFECT_AT NUMBER(10) default 0 not null,
	PRO_ID VARCHAR2(32) default '',
	USED_PRO_ID NUMBER(1) default 0 not null,
	SUBSIDY NUMBER(16,2) default 0.00 not null,
	IS_DELETE NUMBER(10) default 0 not null,
	DATA_PACKAGE NUMBER(10) default 0 not null,
	TOTAL_PAY_AMOUNT NUMBER(16,2) default 0.00 not null,
	TOTAL_PAY_PV NUMBER(16,2) default 0.00 not null,
	EXCHANGED_AMOUNT NUMBER(16,2) default 0.00 not null,
	EXCHANGED_PV NUMBER(16,2) default 0.00 not null,
	IS_SUPPLY NUMBER(1) default 0 not null,
	TURN_OVER_AMOUNT NUMBER(16,2) default 0.00 not null
)
/

comment on table AR_ORDER is '商品订单'
/

comment on column AR_ORDER.SN is '订单号'
/

comment on column AR_ORDER.ORI_SN is '原订单号'
/

comment on column AR_ORDER.USER_ID is '用户ID'
/

comment on column AR_ORDER.USER_NAME is '会员编号'
/

comment on column AR_ORDER.REAL_NAME is '会员姓名'
/

comment on column AR_ORDER.USER_DEC_LV is '会员级别'
/

comment on column AR_ORDER.NEW_USER_ID is '首购会员ID'
/

comment on column AR_ORDER.STATUS is '订单状态'
/

comment on column AR_ORDER.DELIVERY_ID is '配送方式'
/

comment on column AR_ORDER.FREIGHT is '运费'
/

comment on column AR_ORDER.FREE_FREIGHT is '免邮金额'
/

comment on column AR_ORDER.CONSIGNEE is '收货人'
/

comment on column AR_ORDER.MOBILE is '收货人手机'
/

comment on column AR_ORDER.TEL is '固定电话'
/

comment on column AR_ORDER.PROVINCE is '省份名称'
/

comment on column AR_ORDER.CITY is '城市名称'
/

comment on column AR_ORDER.COUNTY is '县区'
/

comment on column AR_ORDER.ADDRESS is '详细地址'
/

comment on column AR_ORDER.ORDER_AMOUNT is '订单总价格'
/

comment on column AR_ORDER.PAY_AMOUNT is '支付价格'
/

comment on column AR_ORDER.PV is '订货PV'
/

comment on column AR_ORDER.PAY_PV is '实付PV'
/

comment on column AR_ORDER.CREATED_AT is '订单创建时间'
/

comment on column AR_ORDER.PAY_AT is '支付时间'
/

comment on column AR_ORDER.GRABED is '是否被抓取'
/

comment on column AR_ORDER.GRAB_AT is '抓取时间'
/

comment on column AR_ORDER.EXPRESS_ID is '快递公司'
/

comment on column AR_ORDER.TRACK_NO is '快递单号'
/

comment on column AR_ORDER.SPLIT_NUMS is '拆分次数'
/

comment on column AR_ORDER.SPLIT_AT is '拆分时间'
/

comment on column AR_ORDER.REMARK is '备注'
/

comment on column AR_ORDER.REBATE is '返利金额'
/

comment on column AR_ORDER.ORDER_TYPE is '订单类型'
/

comment on column AR_ORDER.WALLET_TYPE is '钱包类型'
/

comment on column AR_ORDER.MERGE_SN is '合并单号'
/

comment on column AR_ORDER.MERGE_AT is '合并时间'
/

comment on column AR_ORDER.EXCHANGE_DIFF_AMOUNT is '换货差额'
/

comment on column AR_ORDER.ALLOW_EXCHANGE is '是否允许换货'
/

comment on column AR_ORDER.EXCHANGE_AT is '换货时间'
/

comment on column AR_ORDER.ORDER_MAIN is '订货主体'
/

comment on column AR_ORDER.PERIOD_AT is '订货期数'
/

comment on column AR_ORDER.DELIVERY_PERIOD is '发货期数'
/

comment on column AR_ORDER.DELIVERY_AT is '发货时间'
/

comment on column AR_ORDER.DELIVERY_SUB_COM is '发货所属子公司'
/

comment on column AR_ORDER.DELIVERY_STATUS is '发货状态'
/

comment on column AR_ORDER.UPDATED_AT is '更新时间'
/

comment on column AR_ORDER.UPDATER is '更新人'
/

comment on column AR_ORDER.MAIN_SUB_COM is '订货主体所属子公司'
/

comment on column AR_ORDER.FLOW_TO_USER_ID is '流向主体'
/

comment on column AR_ORDER.FLOW_TO_USER_LV is '流向主体级别'
/

comment on column AR_ORDER.CREATER is '订单创建人'
/

comment on column AR_ORDER.IS_FIRST_PAY is '是否为第一次支付'
/

comment on column AR_ORDER.DATA_AMOUNT is '资料金额'
/

comment on column AR_ORDER.IS_TAKE_EFFECT is '是否生效'
/

comment on column AR_ORDER.PARTITION_DATE is '分区日期'
/

comment on column AR_ORDER.TEMP_DATA is '临时数据'
/

comment on column AR_ORDER.TAKE_EFFECT_AT is '生效时间'
/

comment on column AR_ORDER.PRO_ID is '赠送的优惠券'
/

comment on column AR_ORDER.USED_PRO_ID is '使用的优惠券'
/

comment on column AR_ORDER.SUBSIDY is '补贴'
/

comment on column AR_ORDER.IS_DELETE is '是否删除'
/

comment on column AR_ORDER.DATA_PACKAGE is '资料套'
/

comment on column AR_ORDER.TOTAL_PAY_AMOUNT is '订单总金额(包含拆单的)'
/

comment on column AR_ORDER.TOTAL_PAY_PV is '订单总PV（包含拆单的）'
/

comment on column AR_ORDER.EXCHANGED_AMOUNT is '换货后的金额'
/

comment on column AR_ORDER.EXCHANGED_PV is '换货后的PV'
/

comment on column AR_ORDER.IS_SUPPLY is '是否为补差价订单'
/

comment on column AR_ORDER.TURN_OVER_AMOUNT is '周转报单款'
/

create index AR_ORDER_IDX_ORISN
	on AR_ORDER (ORI_SN)
/

create index AR_ORDER_IDX_UID
	on AR_ORDER (USER_ID)
/

create index AR_ORDER_IDX_UN
	on AR_ORDER (USER_NAME)
/

create index AR_ORDER_IDX_AT
	on AR_ORDER (CREATED_AT)
/

create index AR_ORDER_IDX_DEL
	on AR_ORDER (IS_DELETE)
/

-- 报单
DROP TABLE AR_DEC_ORDER;
CREATE TABLE AR_DEC_ORDER
(
	ID          VARCHAR2(32) DEFAULT SYS_GUID() NOT NULL PRIMARY KEY,
	DEC_SN 			VARCHAR2(20) 							  		NOT NULL,
	ORDER_SN    VARCHAR2(20)                    NOT NULL,
	USER_ID     VARCHAR2(32)                    NOT NULL,
	TO_USER_ID  VARCHAR2(32)                    NOT NULL,
	TYPE     		VARCHAR2(32)                    NOT NULL,
	IS_UPGRADE  NUMBER(1) DEFAULT 0							NOT NULL,
	IS_ADMIN	  NUMBER(1) DEFAULT 0							NOT NULL,
	DEC_AMOUNT  NUMBER(16, 3) DEFAULT 0.000     NOT NULL,
	DEC_PV      NUMBER(16, 3) DEFAULT 0.000     NOT NULL,
	PAID_WALLET VARCHAR2(48)										NOT NULL,
	PERIOD_NUM  NUMBER(10)                      NOT NULL,
	IS_DEL      NUMBER(1) DEFAULT 0             NOT NULL,
	IS_TAKE_EFFECT NUMBER(1) DEFAULT 0          NOT NULL,
	P_CALC_MONTH DATE             							NOT NULL,
	CREATED_AT  NUMBER(10)                      NOT NULL,
	DELETED_AT  NUMBER(10) DEFAULT 0            NOT NULL,
	TAKE_EFFECT_AT NUMBER(10) DEFAULT 0            NOT NULL
)
PARTITION BY RANGE(P_CALC_MONTH)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
	PARTITION P0 VALUES LESS THAN (TO_DATE('2018-03-01','YYYY-MM-DD'))
);
COMMENT ON TABLE AR_DEC_ORDER IS '报单';
COMMENT ON COLUMN AR_DEC_ORDER.DEC_SN IS '报单编号';
COMMENT ON COLUMN AR_DEC_ORDER.ORDER_SN IS '订单编号';
COMMENT ON COLUMN AR_DEC_ORDER.USER_ID IS '会员ID';
COMMENT ON COLUMN AR_DEC_ORDER.TO_USER_ID IS '报单对象ID';
COMMENT ON COLUMN AR_DEC_ORDER.TYPE IS '报单类型';
COMMENT ON COLUMN AR_DEC_ORDER.IS_UPGRADE IS '是否升级单';
COMMENT ON COLUMN AR_DEC_ORDER.IS_ADMIN IS '是否管理员操作';
COMMENT ON COLUMN AR_DEC_ORDER.DEC_AMOUNT IS '报单金额';
COMMENT ON COLUMN AR_DEC_ORDER.DEC_PV IS '报单PV';
COMMENT ON COLUMN AR_DEC_ORDER.PAID_WALLET IS '支付钱包';
COMMENT ON COLUMN AR_DEC_ORDER.PERIOD_NUM IS '报单期数';
COMMENT ON COLUMN AR_DEC_ORDER.IS_DEL IS '是否删除';
COMMENT ON COLUMN AR_DEC_ORDER.IS_TAKE_EFFECT IS '是否有效';
COMMENT ON COLUMN AR_DEC_ORDER.P_CALC_MONTH IS '分区结算月';
COMMENT ON COLUMN AR_DEC_ORDER.CREATED_AT IS '创建时间';
COMMENT ON COLUMN AR_DEC_ORDER.DELETED_AT IS '删除时间';
COMMENT ON COLUMN AR_DEC_ORDER.TAKE_EFFECT_AT IS '生效时间';

CREATE UNIQUE INDEX AR_D_O_DECLARATION_SN_UNINDEX ON AR_DEC_ORDER(DEC_SN);
CREATE UNIQUE INDEX AR_D_O_ORDER_SN_UNINDEX ON AR_DEC_ORDER(ORDER_SN);
CREATE INDEX AR_D_O_USER_ID_INDEX ON AR_DEC_ORDER(USER_ID) LOCAL;
CREATE INDEX AR_D_O_TYPE_INDEX ON AR_DEC_ORDER(TYPE) LOCAL;
CREATE INDEX AR_D_O_PERIOD_NUM_INDEX ON AR_DEC_ORDER(PERIOD_NUM) LOCAL;
CREATE INDEX AR_D_O_IS_DEL_INDEX ON AR_DEC_ORDER(IS_DEL) LOCAL;

-- 订货单
CREATE TABLE AR_ORDER_FORM
(
	ID          VARCHAR2(32) DEFAULT SYS_GUID() NOT NULL PRIMARY KEY,
	SN    			VARCHAR2(20)                    NOT NULL,
	ORDER_SN    VARCHAR2(20)                    NOT NULL,
	USER_ID     VARCHAR2(32)                    NOT NULL,
	TO_USER_ID     VARCHAR2(32)                 NOT NULL,
	ORDER_AMOUNT  NUMBER(16, 3) DEFAULT 0.000   NOT NULL,
	ORDER_PV    NUMBER(16, 3) DEFAULT 0.000     NOT NULL,
	FL_PV       NUMBER(16, 3) DEFAULT 0.000     NOT NULL,
	BT_PV       NUMBER(16, 3) DEFAULT 0.000     NOT NULL,
	PAID_WALLET VARCHAR2(48) 										NOT NULL,
	PERIOD_NUM  NUMBER(10)                      NOT NULL,
	IS_DEL      NUMBER(1) DEFAULT 0             NOT NULL,
	P_CALC_MONTH DATE             							NOT NULL,
	CREATED_AT  NUMBER(10)                      NOT NULL
)
PARTITION BY RANGE(P_CALC_MONTH)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
	PARTITION P0 VALUES LESS THAN (TO_DATE('2018-03-01','YYYY-MM-DD'))
);
COMMENT ON TABLE AR_ORDER_FORM IS '订货单';
COMMENT ON COLUMN AR_ORDER_FORM.SN IS '订货单编号';
COMMENT ON COLUMN AR_ORDER_FORM.ORDER_SN IS '订单编号';
COMMENT ON COLUMN AR_ORDER_FORM.USER_ID IS '会员ID';
COMMENT ON COLUMN AR_ORDER_FORM.TO_USER_ID IS '报单对象ID';
COMMENT ON COLUMN AR_ORDER_FORM.ORDER_AMOUNT IS '订货单金额';
COMMENT ON COLUMN AR_ORDER_FORM.ORDER_PV IS '订货单PV';
COMMENT ON COLUMN AR_ORDER_FORM.FL_PV IS '返利PV';
COMMENT ON COLUMN AR_ORDER_FORM.BT_PV IS '补贴PV';
COMMENT ON COLUMN AR_ORDER_FORM.PAID_WALLET IS '支付的钱包';
COMMENT ON COLUMN AR_ORDER_FORM.PERIOD_NUM IS '订货单所在期数';
COMMENT ON COLUMN AR_ORDER_FORM.IS_DEL IS '状态';
COMMENT ON COLUMN AR_ORDER_FORM.P_CALC_MONTH IS '分区结算月';
COMMENT ON COLUMN AR_ORDER_FORM.CREATED_AT IS '创建时间';

CREATE UNIQUE INDEX AR_O_F_SN_UNINDEX ON AR_ORDER_FORM(SN);
CREATE UNIQUE INDEX AR_O_F_ORDER_SN_UNINDEX ON AR_ORDER_FORM(ORDER_SN);
CREATE INDEX AR_O_F_USER_ID_INDEX ON AR_ORDER_FORM(USER_ID) LOCAL;
CREATE INDEX AR_O_F_PERIOD_NUM_INDEX ON AR_ORDER_FORM(PERIOD_NUM) LOCAL;
CREATE INDEX AR_O_F_IS_DEL_INDEX ON AR_ORDER_FORM(IS_DEL) LOCAL;

-- 报单级别
BEGIN
	EXECUTE IMMEDIATE 'DROP TABLE AR_DECLARATION_LEVEL';
	EXCEPTION WHEN OTHERS THEN NULL;
END;
CREATE TABLE AR_DECLARATION_LEVEL
(
	ID         VARCHAR2(32) DEFAULT SYS_GUID() NOT NULL PRIMARY KEY,
	LEVEL_NAME VARCHAR2(48)                    NOT NULL,
	PERF       NUMBER(16, 2) DEFAULT 0.00      NOT NULL,
	QY_PERCENT NUMBER(16, 2) DEFAULT 0.00      NOT NULL,
	QY_TOUCH_CAP NUMBER(16, 2) DEFAULT 0.00    NOT NULL,
	QY_BIG_CAP NUMBER(16, 2) DEFAULT 0.00      NOT NULL,
	FX_MIN_DEEP NUMBER(10) DEFAULT 1           NOT NULL,
	FX_MAX_DEEP NUMBER(10) DEFAULT 1           NOT NULL,
	FX_PERCENT NUMBER(16, 2) DEFAULT 0.00      NOT NULL,
	IS_ADJUST_GIFT NUMBER(1) DEFAULT 0         NOT NULL,
	IS_DEC     NUMBER(1) DEFAULT 0             NOT NULL,
	SORT       NUMBER(10) DEFAULT 0            NOT NULL,
	CREATED_AT NUMBER(10)                      NOT NULL,
	UPDATED_AT NUMBER(10) DEFAULT 0            NOT NULL
);
COMMENT ON TABLE AR_DECLARATION_LEVEL IS '报单级别';
COMMENT ON COLUMN AR_DECLARATION_LEVEL.LEVEL_NAME IS '级别名称';
COMMENT ON COLUMN AR_DECLARATION_LEVEL.PERF IS '累计业绩';
COMMENT ON COLUMN AR_DECLARATION_LEVEL.QY_PERCENT IS '区域津贴百分比';
COMMENT ON COLUMN AR_DECLARATION_LEVEL.QY_TOUCH_CAP IS '对碰封顶';
COMMENT ON COLUMN AR_DECLARATION_LEVEL.QY_BIG_CAP IS '大区封顶';
COMMENT ON COLUMN AR_DECLARATION_LEVEL.FX_MIN_DEEP IS '复销奖拿奖最小层数';
COMMENT ON COLUMN AR_DECLARATION_LEVEL.FX_MAX_DEEP IS '复销奖拿奖最大层数';
COMMENT ON COLUMN AR_DECLARATION_LEVEL.FX_PERCENT IS '复销奖百分比';
COMMENT ON COLUMN AR_DECLARATION_LEVEL.IS_ADJUST_GIFT IS '是否可调整礼包';
COMMENT ON COLUMN AR_DECLARATION_LEVEL.IS_DEC IS '是否可以报单';
COMMENT ON COLUMN AR_DECLARATION_LEVEL.SORT IS '级别排序';
COMMENT ON COLUMN AR_DECLARATION_LEVEL.CREATED_AT IS '创建时间';
COMMENT ON COLUMN AR_DECLARATION_LEVEL.UPDATED_AT IS '更新时间';

CREATE UNIQUE INDEX AR_DEC_L_L_NAME_UNINDEX ON AR_DECLARATION_LEVEL(LEVEL_NAME);

INSERT INTO AR_DECLARATION_LEVEL (ID, LEVEL_NAME, PERF, QY_PERCENT, QY_TOUCH_CAP, QY_BIG_CAP, FX_MIN_DEEP, FX_MAX_DEEP, FX_PERCENT, IS_ADJUST_GIFT, IS_DEC, SORT, CREATED_AT, UPDATED_AT) VALUES ('67AB99FDC4B6501CE055736AECE8644D', '普通会员', 600.00, 8.00, 4000.00, 100000.00, 1, 12, 3.00, 0, 0, 1, 1521357308, 0);
INSERT INTO AR_DECLARATION_LEVEL (ID, LEVEL_NAME, PERF, QY_PERCENT, QY_TOUCH_CAP, QY_BIG_CAP, FX_MIN_DEEP, FX_MAX_DEEP, FX_PERCENT, IS_ADJUST_GIFT, IS_DEC, SORT, CREATED_AT, UPDATED_AT) VALUES ('67ABCB8C1F7D5519E055736AECE8644D', '银卡会员', 3000.00, 10.00, 15000.00, 280000.00, 1, 13, 3.00, 0, 0, 2, 1521358140, 1521365472);
INSERT INTO AR_DECLARATION_LEVEL (ID, LEVEL_NAME, PERF, QY_PERCENT, QY_TOUCH_CAP, QY_BIG_CAP, FX_MIN_DEEP, FX_MAX_DEEP, FX_PERCENT, IS_ADJUST_GIFT, IS_DEC, SORT, CREATED_AT, UPDATED_AT) VALUES ('67ABCCE20A9F5553E055736AECE8644D', '金卡会员', 6000.00, 12.00, 30000.00, 550000.00, 1, 14, 3.00, 0, 0, 3, 1521358162, 0);
INSERT INTO AR_DECLARATION_LEVEL (ID, LEVEL_NAME, PERF, QY_PERCENT, QY_TOUCH_CAP, QY_BIG_CAP, FX_MIN_DEEP, FX_MAX_DEEP, FX_PERCENT, IS_ADJUST_GIFT, IS_DEC, SORT, CREATED_AT, UPDATED_AT) VALUES ('67ABCE0ECE705575E055736AECE8644D', 'VIP会员', 12000.00, 16.00, 80000.00, 1050000.00, 1, 17, 3.00, 0, 0, 4, 1521358182, 0);

COMMIT ;

-- 聘级
BEGIN
	EXECUTE IMMEDIATE 'DROP TABLE AR_EMPLOY_LEVEL';
	EXCEPTION WHEN OTHERS THEN NULL;
END;
CREATE TABLE AR_EMPLOY_LEVEL
(
	ID          VARCHAR2(32) DEFAULT SYS_GUID() NOT NULL PRIMARY KEY,
	LEVEL_NAME  VARCHAR2(48)                    NOT NULL,
	LOCATION_NUM NUMBER(10) DEFAULT 0           NOT NULL,
	LOCATION_PERF NUMBER(16, 2) DEFAULT 0.00    NOT NULL,
	NEW_PERF    NUMBER(16, 2) DEFAULT 0.00      NOT NULL,
	MIN_EMPLOY_LEVEL VARCHAR2(32) ,
	YC_PERCENT_ARR  VARCHAR2(4000),
	CF_BASE_NUM NUMBER(16, 2) DEFAULT 0.00      NOT NULL,
	CF_PERCENT  NUMBER(16, 2) DEFAULT 0.00      NOT NULL,
	LX_BASE_NUM NUMBER(16, 2) DEFAULT 0.00      NOT NULL,
	LX_PERCENT  NUMBER(16, 2) DEFAULT 0.00      NOT NULL,
	SORT        NUMBER(10)  DEFAULT 0           NOT NULL,
	CREATED_AT  NUMBER(10)                      NOT NULL,
	UPDATED_AT  NUMBER(10) DEFAULT 0            NOT NULL,
	ICON_TYPE  	NUMBER(1) DEFAULT 0             NOT NULL,
	ICON_NUM  	NUMBER(2) DEFAULT 0             NOT NULL
);
COMMENT ON TABLE AR_EMPLOY_LEVEL IS '聘级';
COMMENT ON COLUMN AR_EMPLOY_LEVEL.LEVEL_NAME IS '级别名称';
COMMENT ON COLUMN AR_EMPLOY_LEVEL.LOCATION_NUM IS '区数量';
COMMENT ON COLUMN AR_EMPLOY_LEVEL.LOCATION_PERF IS '单区业绩';
COMMENT ON COLUMN AR_EMPLOY_LEVEL.NEW_PERF IS '新增业绩';
COMMENT ON COLUMN AR_EMPLOY_LEVEL.MIN_EMPLOY_LEVEL IS '几个区域内分别有几个最低什么聘级的会员';
COMMENT ON COLUMN AR_EMPLOY_LEVEL.YC_PERCENT_ARR IS '育成津贴比例集合';
COMMENT ON COLUMN AR_EMPLOY_LEVEL.CF_BASE_NUM IS '车房奖基数';
COMMENT ON COLUMN AR_EMPLOY_LEVEL.CF_PERCENT IS '车房奖系数';
COMMENT ON COLUMN AR_EMPLOY_LEVEL.LX_BASE_NUM IS '领袖奖奖基数';
COMMENT ON COLUMN AR_EMPLOY_LEVEL.LX_PERCENT IS '领袖奖系数';
COMMENT ON COLUMN AR_EMPLOY_LEVEL.SORT IS '排序';
COMMENT ON COLUMN AR_EMPLOY_LEVEL.CREATED_AT IS '创建时间';
COMMENT ON COLUMN AR_EMPLOY_LEVEL.UPDATED_AT IS '更新时间';
COMMENT ON COLUMN AR_EMPLOY_LEVEL.ICON_TYPE IS '图表类型';
COMMENT ON COLUMN AR_EMPLOY_LEVEL.ICON_NUM IS '图标个数';

CREATE UNIQUE INDEX AR_E_L_LEVEL_NAME_UNINDEX ON AR_EMPLOY_LEVEL(LEVEL_NAME);

INSERT INTO AR_EMPLOY_LEVEL (ID, LEVEL_NAME, LOCATION_NUM, LOCATION_PERF, NEW_PERF, MIN_EMPLOY_LEVEL, YC_PERCENT_ARR, CF_BASE_NUM, CF_PERCENT, LX_BASE_NUM, LX_PERCENT, SORT, CREATED_AT, UPDATED_AT) VALUES ('67BE1C6C21F81B32E055736AECE8644D', '无聘级', 0, 0.00, 0.00, null, '[0,0,0,0,0,0,0,0,0,0]', 0.00, 0.00, 0.00, 0.00, 0, 1521436806, 0);
INSERT INTO AR_EMPLOY_LEVEL (ID, LEVEL_NAME, LOCATION_NUM, LOCATION_PERF, NEW_PERF, MIN_EMPLOY_LEVEL, YC_PERCENT_ARR, CF_BASE_NUM, CF_PERCENT, LX_BASE_NUM, LX_PERCENT, SORT, CREATED_AT, UPDATED_AT) VALUES ('67BE5FE7857C216AE055736AECE8644D', '高级经理', 2, 70000.00, 0.00, null, '[0,0,0,0,0,0,0,0,0,0]', 0.00, 0.00, 0.00, 0.00, 1, 1521437938, 0);
INSERT INTO AR_EMPLOY_LEVEL (ID, LEVEL_NAME, LOCATION_NUM, LOCATION_PERF, NEW_PERF, MIN_EMPLOY_LEVEL, YC_PERCENT_ARR, CF_BASE_NUM, CF_PERCENT, LX_BASE_NUM, LX_PERCENT, SORT, CREATED_AT, UPDATED_AT) VALUES ('67BE6A4D03C52288E055736AECE8644D', '合格高级经理', 2, 70000.00, 14000.00, null, '[0,0,0,0,0,0,0,0,0,0]', 0.00, 0.00, 0.00, 0.00, 2, 1521438113, 0);
INSERT INTO AR_EMPLOY_LEVEL (ID, LEVEL_NAME, LOCATION_NUM, LOCATION_PERF, NEW_PERF, MIN_EMPLOY_LEVEL, YC_PERCENT_ARR, CF_BASE_NUM, CF_PERCENT, LX_BASE_NUM, LX_PERCENT, SORT, CREATED_AT, UPDATED_AT) VALUES ('67BE6EA2070D22EBE055736AECE8644D', '一钻经理', 1, 70000.00, 14000.00, '67BE6A4D03C52288E055736AECE8644D', '["10",0,0,0,0,0,0,0,0,0]', 0.00, 0.00, 0.00, 0.00, 3, 1521438185, 0);
INSERT INTO AR_EMPLOY_LEVEL (ID, LEVEL_NAME, LOCATION_NUM, LOCATION_PERF, NEW_PERF, MIN_EMPLOY_LEVEL, YC_PERCENT_ARR, CF_BASE_NUM, CF_PERCENT, LX_BASE_NUM, LX_PERCENT, SORT, CREATED_AT, UPDATED_AT) VALUES ('67BE742A336F2370E055736AECE8644D', '二钻经理', 2, 0.00, 0.00, '67BE6A4D03C52288E055736AECE8644D', '["10","10",0,0,0,0,0,0,0,0]', 0.00, 0.00, 0.00, 0.00, 4, 1521438278, 0);
INSERT INTO AR_EMPLOY_LEVEL (ID, LEVEL_NAME, LOCATION_NUM, LOCATION_PERF, NEW_PERF, MIN_EMPLOY_LEVEL, YC_PERCENT_ARR, CF_BASE_NUM, CF_PERCENT, LX_BASE_NUM, LX_PERCENT, SORT, CREATED_AT, UPDATED_AT) VALUES ('67BE805032C22492E055736AECE8644D', '三钻经理', 3, 0.00, 0.00, '67BE6A4D03C52288E055736AECE8644D', '["10","10","10",0,0,0,0,0,0,0]', 0.00, 0.00, 0.00, 0.00, 5, 1521438482, 0);
INSERT INTO AR_EMPLOY_LEVEL (ID, LEVEL_NAME, LOCATION_NUM, LOCATION_PERF, NEW_PERF, MIN_EMPLOY_LEVEL, YC_PERCENT_ARR, CF_BASE_NUM, CF_PERCENT, LX_BASE_NUM, LX_PERCENT, SORT, CREATED_AT, UPDATED_AT) VALUES ('67BE82019BE524CAE055736AECE8644D', '四钻经理', 3, 0.00, 0.00, '67BE6EA2070D22EBE055736AECE8644D', '["10","10","10","5",0,0,0,0,0,0]', 0.00, 0.00, 0.00, 0.00, 6, 1521438510, 0);
INSERT INTO AR_EMPLOY_LEVEL (ID, LEVEL_NAME, LOCATION_NUM, LOCATION_PERF, NEW_PERF, MIN_EMPLOY_LEVEL, YC_PERCENT_ARR, CF_BASE_NUM, CF_PERCENT, LX_BASE_NUM, LX_PERCENT, SORT, CREATED_AT, UPDATED_AT) VALUES ('67BE84991AF22509E055736AECE8644D', '五钻经理', 3, 0.00, 0.00, '67BE742A336F2370E055736AECE8644D', '["10","10","10","5","5",0,0,0,0,0]', 0.00, 0.00, 0.00, 0.00, 7, 1521438554, 0);
INSERT INTO AR_EMPLOY_LEVEL (ID, LEVEL_NAME, LOCATION_NUM, LOCATION_PERF, NEW_PERF, MIN_EMPLOY_LEVEL, YC_PERCENT_ARR, CF_BASE_NUM, CF_PERCENT, LX_BASE_NUM, LX_PERCENT, SORT, CREATED_AT, UPDATED_AT) VALUES ('67BE866A22C6252FE055736AECE8644D', '六钻经理', 3, 0.00, 0.00, '67BE84991AF22509E055736AECE8644D', '["10","10","10","5","5","5",0,0,0,0]', 0.00, 0.00, 0.00, 0.00, 8, 1521438584, 0);
INSERT INTO AR_EMPLOY_LEVEL (ID, LEVEL_NAME, LOCATION_NUM, LOCATION_PERF, NEW_PERF, MIN_EMPLOY_LEVEL, YC_PERCENT_ARR, CF_BASE_NUM, CF_PERCENT, LX_BASE_NUM, LX_PERCENT, SORT, CREATED_AT, UPDATED_AT) VALUES ('67BE88C8F55F2582E055736AECE8644D', '七钻经理', 3, 0.00, 0.00, '67BE866A22C6252FE055736AECE8644D', '["10","10","10","5","5","5","5",0,0,0]', 0.00, 0.00, 0.00, 0.00, 9, 1521438624, 0);
INSERT INTO AR_EMPLOY_LEVEL (ID, LEVEL_NAME, LOCATION_NUM, LOCATION_PERF, NEW_PERF, MIN_EMPLOY_LEVEL, YC_PERCENT_ARR, CF_BASE_NUM, CF_PERCENT, LX_BASE_NUM, LX_PERCENT, SORT, CREATED_AT, UPDATED_AT) VALUES ('67BE8A92393225B1E055736AECE8644D', '七星董事', 3, 0.00, 0.00, '67BE88C8F55F2582E055736AECE8644D', '["10","10","10","5","5","5","5","5",0,0]', 0.00, 0.00, 0.00, 0.00, 10, 1521438654, 0);
INSERT INTO AR_EMPLOY_LEVEL (ID, LEVEL_NAME, LOCATION_NUM, LOCATION_PERF, NEW_PERF, MIN_EMPLOY_LEVEL, YC_PERCENT_ARR, CF_BASE_NUM, CF_PERCENT, LX_BASE_NUM, LX_PERCENT, SORT, CREATED_AT, UPDATED_AT) VALUES ('67BE8BF76AD925E5E055736AECE8644D', '六星董事', 3, 0.00, 0.00, '67BE8A92393225B1E055736AECE8644D', '["10","10","10","5","5","5","5","5","5",0]', 0.00, 0.00, 0.00, 0.00, 11, 1521438677, 0);
INSERT INTO AR_EMPLOY_LEVEL (ID, LEVEL_NAME, LOCATION_NUM, LOCATION_PERF, NEW_PERF, MIN_EMPLOY_LEVEL, YC_PERCENT_ARR, CF_BASE_NUM, CF_PERCENT, LX_BASE_NUM, LX_PERCENT, SORT, CREATED_AT, UPDATED_AT) VALUES ('67BE8E20BE1A260FE055736AECE8644D', '三星董事', 3, 0.00, 0.00, '67BE8BF76AD925E5E055736AECE8644D', '["10","10","10","5","5","5","5","5","5","5"]', 0.00, 0.00, 0.00, 0.00, 12, 1521438714, 0);
COMMIT;