cps-doc/10技术支持文档/cps添加百世快递语句.sql

-- 商户百世快递费率

drop table if exists merch_rate_expr_best;

/*==============================================================*/
/* Table: merch_rate_expr_best                                  */
/*==============================================================*/
create table merch_rate_expr_best
(
   merch_best_rate_sn   varchar(22) not null  comment '商户百世费率编号',
   merch_sn             varchar(22) not null  comment '商户编号',
   biz_type             varchar(2)  comment 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部',
   cal_formula_express  char(1)  comment '计算公式:0:重量;1:面单',
   expr_type            varchar(8)  comment '物流运送类型 ,00:普通',
   send_province        varchar(64)  comment '寄件地区 ',
   consignee_province   varchar(64)  comment '送达省份 ',
   consignee_city       varchar(64)  comment '送达城市',
   face_sheet_price     numeric(19,2)  comment '面单价(店提使用)',
   fst_gear_weight      numeric(19,2)  comment '一档重(<=1KG)',
   fst_gear_price       numeric(19,2)  comment '一档价(<=1KG)',
   snd_gear_weight      numeric(19,2)  comment '二档重(>1<=2KG)',
   snd_gear_price       numeric(19,2)  comment '二档价(>1<=2KG)',
   trd_gear_weight      numeric(19,2)  comment '三档重(>2<=3KG)',
   trd_gear_price       numeric(19,2)  comment '三档价(>2<=3KG)',
   four_gear_fst_weight numeric(19,2)  comment '四档首重(>3KG)',
   four_gear_fst_price  numeric(19,2)  comment '四档首重价(>3KG)',
   four_gear_snd_weight numeric(19,2)  comment '四档续重(>3KG)',
   four_gear_snd_price  numeric(19,2)  comment '四档续重价(>3KG)',
   is_valid             char(1)  comment '是否有效,0:有效，1:无效',
   remark               varchar(200)  comment '备注',
   creater_sn           varchar(22)  comment '创建人编号',
   create_time          varchar(19)  comment '创建时间,yyyy-MM-dd HH:mm:ss',
   moder_sn             varchar(22)  comment '修改人编号',
   mod_time             varchar(19)  comment '修改时间,yyyy-MM-dd HH:mm:ss',
   tstm                 timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  comment '时间戳',
   primary key (merch_best_rate_sn)
);

alter table merch_rate_expr_best comment '商户百世费率';

/*==============================================================*/
/* Index: index_merch_biz_expr_sp_cp_cc                         */
/*==============================================================*/
create index index_merch_biz_expr_sp_cp_cc on merch_rate_expr_best
(
   merch_sn,
   biz_type,
   expr_type,
   send_province,
   consignee_province,
   consignee_city
);



drop table if exists merch_rate_expr_add_best;

/*==============================================================*/
/* Table: merch_rate_expr_add_best                              */
/*==============================================================*/
create table merch_rate_expr_add_best
(
   merch_best_add_rate_sn varchar(22) not null  comment '商户百世加收费费率编号',
   merch_sn             varchar(22) not null  comment '商户编号',
   biz_type             varchar(2)  comment 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部',
   expr_type            varchar(8)  comment '物流运送类型 ,00:普通',
   consignee_province   varchar(64)  comment '送达省份 ',
   cal_type             char(1)  comment '计算方式:0:按重量;1:一口价',
   fst_weight           numeric(19,2)  comment '首重(<=),包含此重量',
   fst_price            numeric(19,2)  comment '首重价',
   snd_weight           numeric(19,2)  comment '续重',
   snd_price            numeric(19,2)  comment '续重价',
   unified_price        numeric(19,2)  comment '一口价',
   is_valid             char(1)  comment '是否有效,0:有效，1:无效',
   remark               varchar(200)  comment '备注',
   creater_sn           varchar(22)  comment '创建人编号',
   create_time          varchar(19)  comment '创建时间,yyyy-MM-dd HH:mm:ss',
   moder_sn             varchar(22)  comment '修改人编号',
   mod_time             varchar(19)  comment '修改时间,yyyy-MM-dd HH:mm:ss',
   tstm                 timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  comment '时间戳',
   primary key (merch_best_add_rate_sn)
);

alter table merch_rate_expr_add_best comment '商户百世快递加收费率';


drop table if exists merch_rate_expr_insured_best;

/*==============================================================*/
/* Table: merch_rate_expr_insured_best                          */
/*==============================================================*/
create table merch_rate_expr_insured_best
(
   merch_best_insured_rate_sn varchar(22) not null  comment '商户百世保价费率编号',
   merch_sn             varchar(22)  comment '商户编号',
   biz_type             varchar(2)  comment 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部',
   expr_type            varchar(8)  comment '物流运送类型，00：普通',
   consignee_province   varchar(64)  comment '送达省份 ',
   first_gear_min_value numeric(19,2)  comment '一档最小金额(元)',
   first_gear_max_value numeric(19,2)  comment '一档最大金额(<=)(元)',
   first_gear_insured_rate numeric(19,2)  comment '一档保价费(元)',
   two_gear_min_value   numeric(19,2)  comment '二档最小金额(元)',
   two_gear_max_value   numeric(19,2)  comment '二档最大金额(<=)(元)',
   two_gear_insured_rate numeric(19,2)  comment '二档保价费(元)',
   three_gear_min_value numeric(19,2)  comment '三档最小金额(元)',
   three_gear_max_value numeric(19,2)  comment '三档最大金额(<=)(元)',
   three_gear_insured_rate numeric(19,5)  comment '三档保价费率(%)',
   four_gear_min_value  numeric(19,2)  comment '四档最小金额(元)',
   four_gear_max_value  numeric(19,2)  comment '四档最大金额(<=)(元)',
   four_gear_insured_rate numeric(19,5)  comment '四档保价费率(%)',
   insured_minimum_charge numeric(19,2)  comment '保价最低收费（元）',
   is_valid             char(1)  comment '是否有效，0：有效、1：无效',
   remark               varchar(200)  comment '备注',
   creater_sn           varchar(22)  comment '创建人编号',
   create_time          varchar(19)  comment '创建时间,yyyy-MM-dd HH:mm:ss',
   moder_sn             varchar(22)  comment '修改人编号',
   mod_time             varchar(19)  comment '修改时间,yyyy-MM-dd HH:mm:ss',
   tstm                 timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  comment '时间戳',
   primary key (merch_best_insured_rate_sn)
);

alter table merch_rate_expr_insured_best comment '商户百世保价费率';

-- 供应商百世快递费率

drop table if exists supplier_rate_expr_best;

/*==============================================================*/
/* Table: supplier_rate_expr_best                               */
/*==============================================================*/
create table supplier_rate_expr_best
(
   supp_best_rate_sn    varchar(22) not null  comment '供应商百世费率编号',
   comp_sn              varchar(22) not null  comment '公司编号',
   biz_type             varchar(2)  comment 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部',
   expr_type            varchar(8)  comment '物流运送类型 ,00:普通',
   send_province        varchar(64)  comment '寄件地区 ',
   consignee_province   varchar(64)  comment '送达省份 ',
   consignee_city       varchar(64)  comment '送达城市',
   face_sheet_price     numeric(19,2)  comment '面单价(店提使用)',
   fst_gear_weight      numeric(19,2)  comment '一档重(<=1KG)',
   fst_gear_price       numeric(19,2)  comment '一档价(<=1KG)',
   snd_gear_weight      numeric(19,2)  comment '二档重(>1<=2KG)',
   snd_gear_price       numeric(19,2)  comment '二档价(>1<=2KG)',
   trd_gear_weight      numeric(19,2)  comment '三档重(>2<=3KG)',
   trd_gear_price       numeric(19,2)  comment '三档价(>2<=3KG)',
   four_gear_fst_weight numeric(19,2)  comment '四档首重(>3KG)',
   four_gear_fst_price  numeric(19,2)  comment '四档首重价(>3KG)',
   four_gear_snd_weight numeric(19,2)  comment '四档续重(>3KG)',
   four_gear_snd_price  numeric(19,2)  comment '四档续重价(>3KG)',
   is_valid             char(1)  comment '是否有效,0:有效，1:无效',
   remark               varchar(200)  comment '备注',
   creater_sn           varchar(22)  comment '创建人编号',
   create_time          varchar(19)  comment '创建时间,yyyy-MM-dd HH:mm:ss',
   moder_sn             varchar(22)  comment '修改人编号',
   mod_time             varchar(19)  comment '修改时间,yyyy-MM-dd HH:mm:ss',
   tstm                 timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  comment '时间戳',
   primary key (supp_best_rate_sn)
);



/*==============================================================*/
/* Index: index_biz_expr_sp_cp_cc                               */
/*==============================================================*/
create index index_biz_expr_sp_cp_cc on supplier_rate_expr_best
(
   biz_type,
   expr_type,
   send_province,
   consignee_province,
   consignee_city
);


drop table if exists supplier_rate_expr_add_best;

/*==============================================================*/
/* Table: supplier_rate_expr_add_best                           */
/*==============================================================*/
create table supplier_rate_expr_add_best
(
   supp_best_add_rate_sn varchar(22) not null  comment '供应商百世加收费费率编号',
   biz_type             varchar(2)  comment 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部',
   expr_type            varchar(8)  comment '物流运送类型 ,00:普通',
   consignee_province   varchar(64)  comment '送达省份 ',
   cal_type             char(1)  comment '计算方式:0:按重量;1:一口价',
   fst_weight           numeric(19,2)  comment '首重(<=),包含此重量',
   fst_price            numeric(19,2)  comment '首重价',
   snd_weight           numeric(19,2)  comment '续重',
   snd_price            numeric(19,2)  comment '续重价',
   unified_price        numeric(19,2)  comment '一口价',
   is_valid             char(1)  comment '是否有效,0:有效，1:无效',
   remark               varchar(200)  comment '备注',
   creater_sn           varchar(22)  comment '创建人编号',
   create_time          varchar(19)  comment '创建时间,yyyy-MM-dd HH:mm:ss',
   moder_sn             varchar(22)  comment '修改人编号',
   mod_time             varchar(19)  comment '修改时间,yyyy-MM-dd HH:mm:ss',
   tstm                 timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  comment '时间戳',
   primary key (supp_best_add_rate_sn)
);

alter table supplier_rate_expr_add_best comment '供应商百世快递加收费率';



drop table if exists supplier_rate_expr_insured_best;

/*==============================================================*/
/* Table: supplier_rate_expr_insured_best                       */
/*==============================================================*/
create table supplier_rate_expr_insured_best
(
   supplier_best_insured_rate_sn varchar(22) not null  comment '供应商百世保价费率编号',
   biz_type             varchar(2)  comment 'e码头业务类型，【e_biz_type】，00:保税备货， 01:保税直营，02:保税展示补货，03:BC直购，10:保税展示跨境，11:保税展示一般贸易，99:全部',
   expr_type            varchar(8)  comment '物流运送类型，00：普通',
   consignee_province   varchar(64)  comment '送达省份 ',
   first_gear_min_value numeric(19,2)  comment '一档最小金额(元)',
   first_gear_max_value numeric(19,2)  comment '一档最大金额(<=)(元)',
   first_gear_insured_rate numeric(19,2)  comment '一档保价费(元)',
   two_gear_min_value   numeric(19,2)  comment '二档最小金额(元)',
   two_gear_max_value   numeric(19,2)  comment '二档最大金额(<=)(元)',
   two_gear_insured_rate numeric(19,2)  comment '二档保价费(元)',
   three_gear_min_value numeric(19,2)  comment '三档最小金额(元)',
   three_gear_max_value numeric(19,2)  comment '三档最大金额(<=)(元)',
   three_gear_insured_rate numeric(19,5)  comment '三档保价费率(%)',
   four_gear_min_value  numeric(19,2)  comment '四档最小金额(元)',
   four_gear_max_value  numeric(19,2)  comment '四档最大金额(<=)(元)',
   four_gear_insured_rate numeric(19,5)  comment '四档保价费率(%)',
   insured_minimum_charge numeric(19,2)  comment '保价最低收费（元）',
   is_valid             char(1)  comment '是否有效，0：有效、1：无效',
   remark               varchar(200)  comment '备注',
   creater_sn           varchar(22)  comment '创建人编号',
   create_time          varchar(19)  comment '创建时间,yyyy-MM-dd HH:mm:ss',
   moder_sn             varchar(22)  comment '修改人编号',
   mod_time             varchar(19)  comment '修改时间,yyyy-MM-dd HH:mm:ss',
   tstm                 timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  comment '时间戳',
   primary key (supplier_best_insured_rate_sn)
);

alter table supplier_rate_expr_insured_best comment '供应商百世保价费率';



-- 数据增加

INSERT INTO cps_pt.sys_dict_items (item_sn, dict_code, dict_itme, dict_item_name, dict_ord, is_valid, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('diit860458185288159232', 'expFlag', 'best', '百世快递', 7, '0', 'user000000000000000001', '2021-07-02 09:53:37', null, '2021-07-02 09:53:37', '2021-07-02 09:53:37');

-- 菜单

INSERT INTO cps_pt.sys_menu (menu_sn, menu_name, menu_ename, parent_menu_sn, root_menu_sn, menu_tier, menu_url, menu_order, menu_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('menu000000000002021071', '百世费率', 'merch_rate_expr_best', 'menu000000000002002000', 'menu000000000002000000', '3', '#/views/merchant/merchrate/merch-rate-expr-best.html', '311', '0', null, null, null, 'user000000000000000001', '2017-12-11 15:23:30', 'user000000000000000001', '2017-12-11 15:23:30', '2019-03-07 11:44:09');
INSERT INTO cps_pt.sys_menu (menu_sn, menu_name, menu_ename, parent_menu_sn, root_menu_sn, menu_tier, menu_url, menu_order, menu_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('menu000000000002021072', '百世快递保价费率', 'merch_rate_expr_insured_best', 'menu000000000002002000', 'null', '3', '#/views/merchant/merchrate/merch-rate-expr-insured-best.html', '318', '0', null, null, null, 'user000000000000000001', '2020-01-06 10:00:00', 'user000000000000000001', '2020-01-06 10:00:00', '2020-01-06 10:00:00');
INSERT INTO cps_pt.sys_menu (menu_sn, menu_name, menu_ename, parent_menu_sn, root_menu_sn, menu_tier, menu_url, menu_order, menu_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('menu000000000002021073', '百世快递加收费率', 'merch_rate_expr_add_best', 'menu000000000002002000', 'menu000000000002000000', '3', '#/views/merchant/merchrate/merch-rate-expr-add-best.html', '315', '0', null, null, null, 'user000000000000000001', '2019-05-22 09:26:12', 'user000000000000000001', '2019-05-22 09:26:12', '2019-05-22 09:26:13');
INSERT INTO cps_pt.sys_menu (menu_sn, menu_name, menu_ename, parent_menu_sn, root_menu_sn, menu_tier, menu_url, menu_order, menu_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('menu000000000002021074', '百世费率', 'supplier_rate_expr_best', 'menu000000000002004000', 'menu000000000002000000', '3', '#/views/merchant/supplierrate/supplier-rate-expr-best.html', '504', '0', null, null, null, 'user000000000000000001', '2017-12-11 15:23:30', 'user000000000000000001', '2017-12-11 15:23:30', '2019-03-07 11:44:23');
INSERT INTO cps_pt.sys_menu (menu_sn, menu_name, menu_ename, parent_menu_sn, root_menu_sn, menu_tier, menu_url, menu_order, menu_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('menu000000000002021075', '百世快递保价费率', 'supplier_rate_expr_insured_best', 'menu000000000002004000', 'null', '3', '#/views/merchant/supplierrate/supplier-rate-expr-insured-best.html', '511', '0', null, null, null, 'user000000000000000001', '2020-01-06 10:00:00', 'user000000000000000001', '2020-01-06 10:00:00', '2020-01-06 10:00:00');
INSERT INTO cps_pt.sys_menu (menu_sn, menu_name, menu_ename, parent_menu_sn, root_menu_sn, menu_tier, menu_url, menu_order, menu_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('menu000000000002021076', '百世快递加收费率', 'supp_rate_expr_add_best', 'menu000000000002004000', 'menu000000000002000000', '3', '#/views/merchant/supplierrate/supplier-rate-expr-add-best.html', '508', '0', null, null, null, 'user000000000000000001', '2019-05-22 09:27:08', 'user000000000000000001', '2019-05-22 09:27:08', '2019-05-22 09:27:53');

-- 菜单权限
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092325536612352', '新增', 'add', 'menu000000000002021071', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092362178052096', '修改', 'upd', 'menu000000000002021071', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092398886600704', '删除', 'del', 'menu000000000002021071', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func558595184952213504', '导入模板下载', 'template', 'menu000000000002021071', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func558595233899741184', '导入', 'importExcel', 'menu000000000002021071', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());

INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092325536612352', '新增', 'add', 'menu000000000002021072', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092362178052096', '修改', 'upd', 'menu000000000002021072', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092398886600704', '删除', 'del', 'menu000000000002021072', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func558595184952213504', '导入模板下载', 'template', 'menu000000000002021072', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func558595233899741184', '导入', 'importExcel', 'menu000000000002021072', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());

INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092325536612352', '新增', 'add', 'menu000000000002021073', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092362178052096', '修改', 'upd', 'menu000000000002021073', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092398886600704', '删除', 'del', 'menu000000000002021073', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func558595184952213504', '导入模板下载', 'template', 'menu000000000002021073', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func558595233899741184', '导入', 'importExcel', 'menu000000000002021073', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());

INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092325536612352', '新增', 'add', 'menu000000000002021074', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092362178052096', '修改', 'upd', 'menu000000000002021074', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092398886600704', '删除', 'del', 'menu000000000002021074', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func558595184952213504', '导入模板下载', 'template', 'menu000000000002021074', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func558595233899741184', '导入', 'importExcel', 'menu000000000002021074', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());

INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092325536612352', '新增', 'add', 'menu000000000002021075', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092362178052096', '修改', 'upd', 'menu000000000002021075', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092398886600704', '删除', 'del', 'menu000000000002021075', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func558595184952213504', '导入模板下载', 'template', 'menu000000000002021075', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func558595233899741184', '导入', 'importExcel', 'menu000000000002021075', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());

INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092325536612352', '新增', 'add', 'menu000000000002021076', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092362178052096', '修改', 'upd', 'menu000000000002021076', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092398886600704', '删除', 'del', 'menu000000000002021076', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func558595184952213504', '导入模板下载', 'template', 'menu000000000002021076', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func558595233899741184', '导入', 'importExcel', 'menu000000000002021076', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());

INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func172733381458309820', '导入', 'importExcel', 'menu000000000002021076', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:43:01', 'user000000000000000001', '2021-07-05 14:43:01', '2021-07-05 14:43:01');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func287107972416196000', '导入模板下载', 'template', 'menu000000000002021076', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:43:01', 'user000000000000000001', '2021-07-05 14:43:01', '2021-07-05 14:43:01');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func387602169427650200', '删除', 'del', 'menu000000000002021076', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:43:01', 'user000000000000000001', '2021-07-05 14:43:01', '2021-07-05 14:43:01');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func399078676289952060', '新增', 'add', 'menu000000000002021076', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:43:00', 'user000000000000000001', '2021-07-05 14:43:00', '2021-07-05 14:43:00');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func516967634127445200', '修改', 'upd', 'menu000000000002021076', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:43:00', 'user000000000000000001', '2021-07-05 14:43:00', '2021-07-05 14:43:00');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func745895835893131600', '删除', 'del', 'menu000000000002021075', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:43:00', 'user000000000000000001', '2021-07-05 14:43:00', '2021-07-05 14:43:00');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func759475258327531900', '导入', 'importExcel', 'menu000000000002021075', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:43:00', 'user000000000000000001', '2021-07-05 14:43:00', '2021-07-05 14:43:00');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func799432547669329300', '导入模板下载', 'template', 'menu000000000002021075', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:43:00', 'user000000000000000001', '2021-07-05 14:43:00', '2021-07-05 14:43:00');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func343348886485536500', '修改', 'upd', 'menu000000000002021075', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:59', 'user000000000000000001', '2021-07-05 14:42:59', '2021-07-05 14:42:59');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func536021838277598660', '导入', 'importExcel', 'menu000000000002021074', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:59', 'user000000000000000001', '2021-07-05 14:42:59', '2021-07-05 14:42:59');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func590282874731610400', '新增', 'add', 'menu000000000002021075', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:59', 'user000000000000000001', '2021-07-05 14:42:59', '2021-07-05 14:42:59');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func634661209801827800', '修改', 'upd', 'menu000000000002021074', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:59', 'user000000000000000001', '2021-07-05 14:42:59', '2021-07-05 14:42:59');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func663275448105554600', '导入模板下载', 'template', 'menu000000000002021074', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:59', 'user000000000000000001', '2021-07-05 14:42:59', '2021-07-05 14:42:59');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func893451809970151400', '删除', 'del', 'menu000000000002021074', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:59', 'user000000000000000001', '2021-07-05 14:42:59', '2021-07-05 14:42:59');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func269117522304055780', '删除', 'del', 'menu000000000002021073', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:58', 'user000000000000000001', '2021-07-05 14:42:58', '2021-07-05 14:42:58');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func726618088899755900', '新增', 'add', 'menu000000000002021074', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:58', 'user000000000000000001', '2021-07-05 14:42:58', '2021-07-05 14:42:58');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func734699125340859500', '导入模板下载', 'template', 'menu000000000002021073', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:58', 'user000000000000000001', '2021-07-05 14:42:58', '2021-07-05 14:42:58');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func770296171279899900', '修改', 'upd', 'menu000000000002021073', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:58', 'user000000000000000001', '2021-07-05 14:42:58', '2021-07-05 14:42:58');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func966143087452410900', '导入', 'importExcel', 'menu000000000002021073', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:58', 'user000000000000000001', '2021-07-05 14:42:58', '2021-07-05 14:42:58');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func141789825672088030', '删除', 'del', 'menu000000000002021072', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:57', 'user000000000000000001', '2021-07-05 14:42:57', '2021-07-05 14:42:57');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func261051037685061860', '新增', 'add', 'menu000000000002021072', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:57', 'user000000000000000001', '2021-07-05 14:42:57', '2021-07-05 14:42:57');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func344547597546640400', '导入', 'importExcel', 'menu000000000002021072', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:57', 'user000000000000000001', '2021-07-05 14:42:57', '2021-07-05 14:42:57');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func353983409380524800', '导入模板下载', 'template', 'menu000000000002021072', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:57', 'user000000000000000001', '2021-07-05 14:42:57', '2021-07-05 14:42:57');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func384988582213398600', '修改', 'upd', 'menu000000000002021072', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:57', 'user000000000000000001', '2021-07-05 14:42:57', '2021-07-05 14:42:57');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func560787787251908100', '新增', 'add', 'menu000000000002021073', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:57', 'user000000000000000001', '2021-07-05 14:42:57', '2021-07-05 14:42:57');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func135353348809623490', '导入模板下载', 'template', 'menu000000000002021071', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:56', 'user000000000000000001', '2021-07-05 14:42:56', '2021-07-05 14:42:56');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func501114853472555840', '删除', 'del', 'menu000000000002021071', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:56', 'user000000000000000001', '2021-07-05 14:42:56', '2021-07-05 14:42:56');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func756740315404478700', '修改', 'upd', 'menu000000000002021071', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:56', 'user000000000000000001', '2021-07-05 14:42:56', '2021-07-05 14:42:56');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func873422211290730400', '导入', 'importExcel', 'menu000000000002021071', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:56', 'user000000000000000001', '2021-07-05 14:42:56', '2021-07-05 14:42:56');
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) VALUES ('func460862250403372800', '新增', 'add', 'menu000000000002021071', '0', null, null, null, 'user000000000000000001', '2021-07-05 14:42:55', 'user000000000000000001', '2021-07-05 14:42:55', '2021-07-05 14:42:55');

-- 数据字典
INSERT INTO cps_pt.sys_dictionary (dict_sn, dict_code, dict_name, maintain_flag, dict_type, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('dict861630219573538816', 'exprTypeBest', '物流运送类型-百世', '0', '0', 'user000000000000000001', '2021-07-05 15:30:52', 'user000000000000000001', '2021-07-05 15:30:52', '2021-07-05 15:30:49');
INSERT INTO cps_pt.sys_dict_items (item_sn, dict_code, dict_itme, dict_item_name, dict_ord, is_valid, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('diit861630547316453376', 'exprTypeBest', '70', '普通', null, '0', 'user000000000000000001', '2021-07-05 15:32:10', null, '2021-07-05 15:32:10', '2021-07-05 15:32:07');
INSERT INTO cps_pt.sys_dict_items (item_sn, dict_code, dict_itme, dict_item_name, dict_ord, is_valid, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('diit861975877304332288', 'expFlag', 'best', '百世快递', 7, '0', 'user000000000000000001', '2021-07-06 14:24:23', 'user000000000000000001', '2021-07-06 14:24:30', '2021-07-06 14:24:27');

alter table tmp_cp_data_order_pkg modify remark varchar(512) null comment '备注';

-- 门店
alter table tmp_cp_data_cus_tax
   add oms_shop_sn varchar(22) null comment 'oms门店编号' after oms_order_sn;
alter table tmp_cp_data_order_pkg
   add oms_shop_sn varchar(22) null comment 'oms门店编号' after oms_order_sn;
alter table cp_data_order_pkg
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;
alter table cp_data_cus_tax
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;
alter table cp_data_zone_stevedore
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;
alter table cp_data_whse_op
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;
alter table cp_data_cls_cus
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;
alter table cp_data_cmb_zone_chgs
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;
alter table cp_data_trans
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;






-- 门店基础信息
drop table if exists merchant_shop_base;

/*==============================================================*/
/* Table: merchant_shop_base                                    */
/*==============================================================*/
create table merchant_shop_base
(
   shop_sn              varchar(22) not null  comment '门店编号',
   shop_name            varchar(256)  comment '门店名称',
   merch_sn             varchar(22)  comment '商户编号',
   merch_name           varchar(256)  comment '商户名称',
   shop_ename           varchar(256)  comment '门店英文名',
   shop_sname           varchar(128)  comment '门店简称',
   shop_code            varchar(64)  comment '门店识别码',
   oms_shop_sn          varchar(22)  comment 'oms门店账号',
   province             varchar(64)  comment '省',
   city                 varchar(64)  comment '市',
   county               varchar(64)  comment '区县',
   address              varchar(256)  comment '地址',
   contacts             varchar(64)  comment '联系人',
   tel                  varchar(32)  comment '电话',
   mobile               varchar(18)  comment '手机',
   is_valid             char(1)  comment '是否有效,0:有效，1:无效',
   creater_sn           varchar(22)  comment '创建人编号',
   create_time          datetime  comment '创建时间,yyyy-MM-dd HH:mm:ss',
   moder_sn             varchar(22)  comment '修改人编号',
   mod_time             datetime  comment '修改时间,yyyy-MM-dd HH:mm:ss',
   tstm                 timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  comment '时间戳',
   primary key (shop_sn)
);

alter table merchant_shop_base comment '门店基本信息';

/*==============================================================*/
/* Index: idx_1                                                 */
/*==============================================================*/
create index idx_1 on merchant_shop_base
(
   merch_sn
);

/*==============================================================*/
/* Index: idx_2                                                 */
/*==============================================================*/
create index idx_2 on merchant_shop_base
(
   oms_shop_sn
);


INSERT INTO cps_pt.sys_menu (menu_sn, menu_name, menu_ename, parent_menu_sn, root_menu_sn, menu_tier, menu_url, menu_order, menu_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('menu000000000002021077', '门店基本信息', 'merch_shop_base', 'menu000000000002001000', 'menu000000000002000000', '3', '#/views/merchant/merch/merch-shop-base.html', '101', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());

INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092325536612353', '新增', 'add', 'menu000000000002021077', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092362178052097', '修改', 'upd', 'menu000000000002021077', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func580092398886600750', '删除', 'del', 'menu000000000002021077', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func558595184952213505', '启用', 'ena', 'menu000000000002021077', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('func558595233899741185', '不启用', 'dis', 'menu000000000002021077', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());

-- 索引添加
create unique index uk_1
   on supplier_rate_expr_insured_best (consignee_province, biz_type, expr_type);


-- 结算应收费用添加门店
alter table cp_chgs_order_pkg
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;
alter table cp_chgs_cus_tax
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;
alter table cp_chgs_zone_stevedore
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;
alter table cp_chgs_whse_op
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;
alter table cp_chgs_cls_cus
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;
alter table cp_chgs_cmb_zone
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;
alter table cp_chgs_trans
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;

-- 归集应收费用添加门店
alter table cpm_chgs_order_pkg
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;
alter table cpm_chgs_cus_tax
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;
alter table cpm_chgs_whse_op
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;
alter table cpm_chgs_proxy_cls_cus
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;
alter table cpm_chgs_trans
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;

-- 应收费用添加门店
alter table cpcp_col_chgs
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;

-- 操作记录
alter table cp_op_record
   add shop_sn varchar(22) null comment '门店编号' after merch_sn;


drop table if exists cpm_chgs_record;

/*==============================================================*/
/* Table: cpm_chgs_record                                       */
/*==============================================================*/
create table cpm_chgs_record
(
   record_chgs_sn       varchar(22) not null  comment '备案费用编号',
   uni_code             varchar(22)  comment '统一编号，系统每次建帐录入唯一序号，该编号由unic+18位雪花算法编成',
   merch_sn             varchar(22)  comment '商户编号',
   sett_merch_sn        varchar(22) not null  comment '结算商户编号',
   cpm_cate_flag        varchar(32)  comment '归集分类标识，whse_chgs:仓租费，order_pkg_chgs:订单包裹费，whse_op_chgs:库内操作费，cls_cus_chgs:代理通关费，trans_chgs:运输费，cnsmb_chgs:耗材费，cus_tax_chgs：海关税款，pkg_expr_chgs:包裹快递费，cmb_zone_chgs:招商园区费',
   cp_type              char(2)  comment '收支分类，00:应收，10:应付，20:应收应付',
   currency             varchar(18)  comment '币种',
   record_aggr_chgs     numeric(19,2)  comment '备案总额',
   others_chgs          numeric(19,4)  comment '其它金额',
   record_chgs_merge_total numeric(19,2)  comment '备案归集总额',
   has_atta             char(1)  comment '有无附件，0:无，1:有',
   atta_url             varchar(1000)  comment '附件url',
   is_gen_col_pay       char(1)  comment '生成应收应付0:无，1:否，2:是',
   remark               varchar(200)  comment '备注',
   creater_sn           varchar(22)  comment '创建人编号',
   create_time          varchar(19)  comment '创建时间,yyyy-MM-dd HH:mm:ss',
   moder_sn             varchar(22)  comment '修改人编号',
   mod_time             varchar(19)  comment '修改时间,yyyy-MM-dd HH:mm:ss',
   tstm                 timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  comment '时间戳',
   primary key (record_chgs_sn)
);

alter table cpm_chgs_record comment '归集备案费用';

/*==============================================================*/
/* Index: idx_uni_record                                       */
/*==============================================================*/
create unique index idx_uni_record on cpm_chgs_record
(
   uni_code,
   merch_sn
);


-- 应收费用增加备案费
alter table cpcp_col_chgs
   add record_chgs_total  numeric(19,2) null comment '备案总额' after trans_chgs_tax;
alter table cpcp_col_chgs
   add record_chgs_tax  numeric(19,2) null comment '备案开票税额' after record_chgs_total;

-- 商户运输费率
alter table merch_rate_trans
   add mini_trans_price numeric(19,2) null default 0 comment '小型车运输价' after ori_place_type;
alter table merch_rate_trans
   add mini_ret_empty_price numeric(19,2) null default 0 comment '小型车返空价' after mini_trans_price;
alter table merch_rate_trans
   add mini_press_day_price numeric(19,2) null default 0 comment '小型车压日价' after mini_ret_empty_price;
alter table merch_rate_trans
   add mini_press_night_price numeric(19,2) null default 0 comment '小型车压夜价' after mini_press_day_price;


-- 供应商运输费率
alter table supplier_rate_trans
   add mini_trans_price numeric(19,2) null default 0 comment '小型车运输价' after ori_place_type;
alter table supplier_rate_trans
   add mini_ret_empty_price numeric(19,2) null default 0 comment '小型车返空价' after mini_trans_price;
alter table supplier_rate_trans
   add mini_press_day_price numeric(19,2) null default 0 comment '小型车压日价' after mini_ret_empty_price;
alter table supplier_rate_trans
   add mini_press_night_price numeric(19,2) null default 0 comment '小型车压夜价' after mini_press_day_price;

INSERT INTO cps_pt.sys_menu (menu_sn, menu_name, menu_ename, parent_menu_sn, root_menu_sn, menu_tier, menu_url, menu_order, menu_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm) 
VALUES ('menu000000000001005016', '归集备案费', 'cpm_chgs_record', 'menu000000000001006000', 'menu000000000001000000', '3', 
   '#/views/bizpayments/collection/receivablefee/cpm-chgs-record.html', '605', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());


INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm)
 VALUES ('func770296171279899901', '修改', 'upd', 'menu000000000001005016', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm)
 VALUES ('func770296171279899902', '选择生成应付', 'genrcvSelect', 'menu000000000001005016', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());
INSERT INTO cps_pt.sys_function (func_sn, func_name, func_ename, menu_sn, func_status, remarks, deleter_sn, delete_time, creater_sn, create_time, moder_sn, mod_time, tstm)
 VALUES ('func770296171279899903', '全部生成应收', 'genrcvAll', 'menu000000000001005016', '0', null, null, null, 'user000000000000000001', now(), 'user000000000000000001', now(), now());

INSERT INTO cps_pt.sys_dict_items (item_sn, dict_code, dict_itme, dict_item_name, dict_ord, is_valid, creater_sn, create_time, moder_sn, mod_time, tstm) 
 VALUES ('diit867072636884762624', 'vhclType', '09', '小型车', 0, '0', 'user000000000000000001', now(), null, now(), now());


 drop index idx_uni_pkg_op on cpm_chgs_order_pkg;

create unique index idx_uni_pkg_op
   on cpm_chgs_order_pkg (uni_code, merch_sn, shop_sn);

drop index idx_uni_cus_tax on cpm_chgs_cus_tax;

create unique index idx_uni_cus_tax
   on cpm_chgs_cus_tax (uni_code, merch_sn, shop_sn);


drop index idx_uni_cls_cus on cpm_chgs_proxy_cls_cus;

create unique index idx_uni_cls_cus
   on cpm_chgs_proxy_cls_cus (uni_code, merch_sn, shop_sn);

drop index idx_uni_trans on cpm_chgs_trans;

create unique index idx_uni_trans
   on cpm_chgs_trans (uni_code, merch_sn, shop_sn);


drop index idx_uni_whse_op on cpm_chgs_whse_op;

create unique index idx_uni_whse_op
   on cpm_chgs_whse_op (uni_code, merch_sn, shop_sn);

drop index idx_uni_whse on cpcp_col_chgs;

create unique index idx_uni_whse
   on cpcp_col_chgs (uni_code, merch_sn, shop_sn);