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;