0300_ddl_gradata_tables.sql

prompt Creating gradata.GRAS_BRANCH_ASSIGN_RULE...
create table gradata.GRAS_BRANCH_ASSIGN_RULE
(
  branch_code  VARCHAR2(10) not null,
  assign_rule  VARCHAR2(2) not null,
  priority     NUMBER(2) not null,
  pk_serial    VARCHAR2(10) not null,
  created_user VARCHAR2(100) not null,
  created_date DATE default SYSDATE not null,
  updated_user VARCHAR2(100) not null,
  updated_date DATE default SYSDATE not null
);
comment on table gradata.GRAS_BRANCH_ASSIGN_RULE
  is '机构分单规则信息表';
comment on column gradata.GRAS_BRANCH_ASSIGN_RULE.branch_code
  is '机构';
comment on column gradata.GRAS_BRANCH_ASSIGN_RULE.assign_rule
  is '分单规则';
comment on column gradata.GRAS_BRANCH_ASSIGN_RULE.priority
  is '优先级';
comment on column gradata.GRAS_BRANCH_ASSIGN_RULE.created_user
  is '录入人';
comment on column gradata.GRAS_BRANCH_ASSIGN_RULE.created_date
  is '录入日期';
comment on column gradata.GRAS_BRANCH_ASSIGN_RULE.updated_user
  is '修改人';
comment on column gradata.GRAS_BRANCH_ASSIGN_RULE.updated_date
  is '修改日期';
comment on column gradata.GRAS_BRANCH_ASSIGN_RULE.pk_serial
  is '数据主键';
alter table gradata.GRAS_BRANCH_ASSIGN_RULE
  add constraint PK_GRAS_BRANCH_ASSIGN_RULE primary key (BRANCH_CODE,PRIORITY);
alter table gradata.GRAS_BRANCH_ASSIGN_RULE
  add constraint FK_GRAS_BRAN_ASS_RUL_BRA_CODE foreign key (BRANCH_CODE)
  references SLISINTF.BRANCH_INFO(BRANCH_CODE);
alter table gradata.GRAS_BRANCH_ASSIGN_RULE
  add constraint FK_GRAS_BRAN_ASS_RUL_ASS_RUL foreign key (ASSIGN_RULE)
  references GRARULE.GRAS_ASSIGN_RULE(ASSIGN_RULE);

prompt Creating gradata.GRAS_AREA_STAFF...
create table gradata.GRAS_AREA_STAFF
(
  area_no      VARCHAR2(10) not null,
  channel_type VARCHAR2(2) not null,
  is_valid     VARCHAR2(1) default 'Y' not null,
  servicer_no  VARCHAR2(20) not null,
  pk_serial    VARCHAR2(10) not null,
  created_user VARCHAR2(100) not null,
  created_date DATE default SYSDATE not null,
  updated_user VARCHAR2(100) not null,
  updated_date DATE default SYSDATE not null
);
comment on table gradata.GRAS_AREA_STAFF
  is '区域人员匹配表';
comment on column gradata.GRAS_AREA_STAFF.area_no
  is '区域代码';
comment on column gradata.GRAS_AREA_STAFF.channel_type
  is '渠道';
comment on column gradata.GRAS_AREA_STAFF.is_valid
  is '是否有效';
comment on column gradata.GRAS_AREA_STAFF.pk_serial
  is '数据主键';
comment on column gradata.GRAS_AREA_STAFF.servicer_no
  is '区域服务人员代码';
comment on column gradata.GRAS_AREA_STAFF.created_user
  is '录入人 ';
comment on column gradata.GRAS_AREA_STAFF.created_date
  is '录入日期';
comment on column gradata.GRAS_AREA_STAFF.updated_user
  is '修改人';
comment on column gradata.GRAS_AREA_STAFF.updated_date
  is '修改日期';
alter table gradata.GRAS_AREA_STAFF
  add constraint PK_GRAS_AREA_STAFF primary key (PK_SERIAL);
alter table gradata.GRAS_AREA_STAFF
  add constraint FK_GRAS_AREA_STA_CHA_TYPE foreign key (CHANNEL_TYPE)
  references SLISINTF.CHANNEL_TYPE_TBL(CHANNEL_TYPE);
alter table gradata.GRAS_AREA_STAFF
  add constraint FK_GRAS_AREA_STA_EMP_NO foreign key (SERVICER_NO)
  references SLISINTF.STAFF_INFO(SERVICER_NO);
alter table gradata.GRAS_AREA_STAFF
  add constraint CK_GRAS_AREA_STAFF_IS_VALID
  check IS_VALID in ('Y','N');

create table gradata.GRAS_POLICY_GEO
(
  policy_no    VARCHAR2(20) not null,
  lon          NUMBER(10,6) default 0.0 not null,
  lat          NUMBER(10,6) default 0.0 not null,
  is_valid     VARCHAR2(1) default 'Y' not null,
  pk_serial    VARCHAR2(10),
  created_user VARCHAR2(100) not null,
  created_date DATE default SYSDATE not null,
  updated_user VARCHAR2(100) not null,
  updated_date DATE default SYSDATE not null
);
comment on table gradata.GRAS_POLICY_GEO
  is '保单地址匹配表';
comment on column gradata.GRAS_POLICY_GEO.policy_no
  is '保单号';
comment on column gradata.GRAS_POLICY_GEO.lon
  is '经度（墨卡托）';
comment on column gradata.GRAS_POLICY_GEO.lat
  is '纬度（墨卡托）';
comment on column gradata.GRAS_POLICY_GEO.created_user
  is '录入人 ';
comment on column gradata.GRAS_POLICY_GEO.created_date
  is '录入日期';
comment on column gradata.GRAS_POLICY_GEO.updated_user
  is '修改人';
comment on column gradata.GRAS_POLICY_GEO.updated_date
  is '修改日期';
comment on column gradata.GRAS_POLICY_GEO.is_valid
  is '是否有效';
comment on column gradata.GRAS_POLICY_GEO.pk_serial
  is '数据主键';
alter table gradata.GRAS_POLICY_GEO
  add constraint PK_GRAS_POLICY_GEO primary key (PK_SERIAL);
alter table gradata.GRAS_POLICY_GEO
  add constraint FK_GRAS_POLICY_GEO_POL_NO foreign key (POLICY_NO)
  references SLISINTF.POLICY(POLICY_NO);
alter table gradata.GRAS_POLICY_GEO
  add constraint CK_GRAS_POLICY_GEO_IS_VALID
  check IS_VALID in ('Y','N');
create index IX_GRAS_POLICY_GEO_1 on gradata.GRAS_POLICY_GEO (POLICY_NO);

create table gradata.GRAS_BRANCH_GEO
(
  branch_code  VARCHAR2(10) not null,
  layer_code   VARCHAR2(10) not null,
  layer_name   VARCHAR2(50) not null,
  paas_serial  VARCHAR2(50) not null,
  lon          NUMBER(10,6) not null,
  lat          NUMBER(10,6) not null,
  pk_serial    VARCHAR2(10) not null,
  created_user VARCHAR2(100) not null,
  created_date DATE default SYSDATE not null,
  updated_user VARCHAR2(100) not null,
  updated_date DATE default SYSDATE not null
);
comment on table gradata.GRAS_BRANCH_GEO
  is '机构地址匹配表';
comment on column gradata.GRAS_BRANCH_GEO.branch_code
  is '机构代码';
comment on column gradata.GRAS_BRANCH_GEO.layer_code
  is '区块编号';
comment on column gradata.GRAS_BRANCH_GEO.layer_name
  is '区块名称';
comment on column gradata.GRAS_BRANCH_GEO.paas_serial
  is 'PAAS序列';
comment on column gradata.GRAS_BRANCH_GEO.created_user
  is '录入人 ';
comment on column gradata.GRAS_BRANCH_GEO.created_date
  is '录入日期';
comment on column gradata.GRAS_BRANCH_GEO.updated_user
  is '修改人 ';
comment on column gradata.GRAS_BRANCH_GEO.updated_date
  is '修改日期';
comment on column gradata.GRAS_BRANCH_GEO.lon
  is '经度';
comment on column gradata.GRAS_BRANCH_GEO.lat
  is '纬度';
alter table gradata.GRAS_BRANCH_GEO
  add constraint PK_GRAS_BRANCH_GEO primary key (PK_SERIAL);
alter table gradata.GRAS_BRANCH_GEO
  add constraint FK_GRAS_BRANCH_GEO_BRA_CODE foreign key (BRANCH_CODE)
  references SLISINTF.BRANCH_INFO(BRANCH_CODE);
create index IX_GRAS_BRANCH_GEO_1 on gradata.GRAS_BRANCH_GEO (BRANCH_CODE);
create index IX_GRAS_BRANCH_GEO_2 on gradata.GRAS_BRANCH_GEO (LAYER_CODE);

create table gradata.GRAS_BANK_GEO
(
  bank_code    VARCHAR2(12) not null,
  lon          NUMBER(10,6) not null,
  lat          NUMBER(10,6) not null,
  pk_serial    VARCHAR2(10) not null,
  created_user VARCHAR2(100) not null,
  created_date DATE default SYSDATE not null,
  updated_user VARCHAR2(100) not null,
  updated_date DATE default SYSDATE not null
);
comment on table gradata.GRAS_BANK_GEO
  is '银行地址匹配表';
comment on column gradata.GRAS_BANK_GEO.bank_code
  is '银行代码';
comment on column gradata.GRAS_BANK_GEO.lon
  is '经度（墨卡托）';
comment on column gradata.GRAS_BANK_GEO.lat
  is '纬度（墨卡托）';
comment on column gradata.GRAS_BANK_GEO.created_user
  is '录入人 ';
comment on column gradata.GRAS_BANK_GEO.created_date
  is '录入日期';
comment on column gradata.GRAS_BANK_GEO.updated_user
  is '修改人';
comment on column gradata.GRAS_BANK_GEO.updated_date
  is '修改日期';
comment on column gradata.GRAS_BANK_GEO.pk_serial
  is '数据主键';
alter table gradata.GRAS_BANK_GEO
  add constraint PK_GRAS_BANK_GEO primary key (PK_SERIAL);
alter table gradata.GRAS_BANK_GEO
  add constraint FK_GRAS_BANK_GEO_BANK_CODE foreign key (BANK_CODE)
  references SLISINTF.BANK_INFO(BANK_CODE);
create index IX_GRAS_BANK_GEO_1 on gradata.GRAS_BANK_GEO (BANK_CODE);

create table gradata.GRAS_BANK_EMP
(
  bank_code    VARCHAR2(12) not null,
  emp_no       VARCHAR2(20) not null,
  begin_date   DATE not null,
  end_date     DATE,
  pk_serial    VARCHAR2(10) not null,
  created_user VARCHAR2(100) not null,
  created_date DATE default SYSDATE not null,
  updated_user VARCHAR2(100) not null,
  updated_date DATE default SYSDATE not null
);
comment on table gradata.GRAS_BANK_EMP
  is '银行与续收人员关系表';
comment on column gradata.GRAS_BANK_EMP.bank_code
  is '银行代码';
comment on column gradata.GRAS_BANK_EMP.emp_no
  is '员工代码';
comment on column gradata.GRAS_BANK_EMP.begin_date
  is '指定起始日期';
comment on column gradata.GRAS_BANK_EMP.end_date
  is '指定终止日期';
comment on column gradata.GRAS_BANK_EMP.pk_serial
  is '数据主键';
comment on column gradata.GRAS_BANK_EMP.created_date
  is '录入日期';
comment on column gradata.GRAS_BANK_EMP.updated_user
  is '修改人  ';
comment on column gradata.GRAS_BANK_EMP.updated_date
  is '修改日期';
comment on column gradata.GRAS_BANK_EMP.created_user
  is '录入人 ';
alter table gradata.GRAS_BANK_EMP
  add constraint PK_gradata.GRAS_BANK_EMP primary key (BANK_CODE,EMP_NO,BEGIN_DATE);
alter table gradata.GRAS_BANK_EMP
  add constraint FK_gradata.GRAS_BANK_EMP_BAN_COD foreign key (BANK_CODE)
  references SLISINTF.BANK_INFO(BANK_CODE);
alter table gradata.GRAS_DEPT_SERVICER
  add constraint FK_GRAS_BANK_EMP_EMP_NO foreign key (EMP_NO)
  references SLISINTF.STAFF_INFO(EMP_NO);

create table gradata.GRAS_BRANCH_STYLE
(
  branch_code  VARCHAR2(10) not null,
  style_code   VARCHAR2(10) not null,
  remark       VARCHAR2(100) ,
  flag         VARCHAR2(1) default '0' not null,
  pk_serial    VARCHAR2(10) not null,
  updated_user VARCHAR2(100) not null,
  updated_date DATE default SYSDATE not null,
  created_user VARCHAR2(100) not null,
  created_date DATE default SYSDATE not null
);
comment on table gradata.GRAS_BRANCH_STYLE
  is '机构参数样式表';
comment on column gradata.GRAS_BRANCH_STYLE.flag
  is '变更标示:1-已变更，0-未变更';
comment on column gradata.GRAS_BRANCH_STYLE.style_code
  is '展示样式属性名';
comment on column gradata.GRAS_BRANCH_STYLE.remark
  is '描述说明';
comment on column gradata.GRAS_BRANCH_STYLE.branch_code
  is '机构代码';
comment on column gradata.GRAS_BRANCH_STYLE.updated_user
  is '修改人  ';
comment on column gradata.GRAS_BRANCH_STYLE.updated_date
  is '修改日期';
alter table gradata.GRAS_BRANCH_STYLE
  add constraint PK_GRAS_BRANCH_STYLE primary key (PK_SERIAL);
alter table gradata.GRAS_BRANCH_STYLE
  add constraint FK_GRAS_BRA_STY_BRA_COD foreign key (BRANCH_CODE)
  references SLISINTF.BRANCH_INFO(BRANCH_CODE);
alter table gradata.GRAS_BRANCH_STYLE
  add constraint FK_GRAS_BRA_STY_BRA_COD foreign key (STYLE_CODE)
  references GRARULE.GRAS_DISPLAY_STYLE(STYLE_CODE);
alter table gradata.GRAS_BRANCH_STYLE
  add constraint CK_GRAS_BRANCH_STYLE_FLAG
  check (FLAG in ('1','0'));

create table gradata.GRAS_ASSIGN_RULE_CHGS
(
  policy_no           VARCHAR2(20) not null,
  branch_code         VARCHAR2(10) not null,
  last_assign_type    VARCHAR2(2),
  next_assign_type    VARCHAR2(2),
  last_assign_value   VARCHAR2(10),
  next_assign_value   VARCHAR2(10),
  change_user         VARCHAR2(50) not null,
  change_date         DATE default SYSDATE not null,
  pk_serial           VARCHAR2(10) not null,
  created_user        VARCHAR2(100) not null,
  created_date        DATE default SYSDATE not null,
  updated_user        VARCHAR2(100) not null,
  updated_date        DATE default SYSDATE not null
);
comment on table gradata.GRAS_ASSIGN_RULE_CHGS
  is '保单分单规则调整记录表';
comment on column gradata.GRAS_ASSIGN_RULE_CHGS.policy_no
  is '保单号';
comment on column gradata.GRAS_ASSIGN_RULE_CHGS.branch_code
  is '机构代码';
comment on column gradata.GRAS_ASSIGN_RULE_CHGS.next_assign_type
  is '调整后分单类型';
comment on column gradata.GRAS_ASSIGN_RULE_CHGS.next_assign_value
  is '调整后值';
comment on column gradata.GRAS_ASSIGN_RULE_CHGS.change_user
  is '调整人员';
comment on column gradata.GRAS_ASSIGN_RULE_CHGS.change_date
  is '调整日期';
comment on column gradata.GRAS_ASSIGN_RULE_CHGS.pk_serial
  is '数字主键';
comment on column gradata.GRAS_ASSIGN_RULE_CHGS.created_user
  is '录入人';
comment on column gradata.GRAS_ASSIGN_RULE_CHGS.created_date
  is '录入日期';
comment on column gradata.GRAS_ASSIGN_RULE_CHGS.updated_user
  is '修改人';
comment on column gradata.GRAS_ASSIGN_RULE_CHGS.updated_date
  is '修改日期';
comment on column gradata.GRAS_ASSIGN_RULE_CHGS.last_assign_type
  is '调整前后分单类型';
comment on column gradata.GRAS_ASSIGN_RULE_CHGS.last_assign_value
  is '调整前值';
alter table gradata.GRAS_ASSIGN_RULE_CHGS
  add constraint PK_GRAS_ASSIGN_RULE_CHGS primary key (PK_SERIAL);
alter table gradata.GRAS_ASSIGN_RULE_CHGS
  add constraint FK_GRAS_ASS_RUL_CH_POL_NO foreign key (POLICY_NO)
  references SLISINTF.POLICY(POLICY_NO);
alter table gradata.GRAS_ASSIGN_RULE_CHGS
  add constraint FK_GRAS_ASS_RUL_CH_LA_ASS_TY foreign key (LAST_ASSIGN_TYPE)
  references GRARULE.GRAS_ASSIGN_TYPE(ASSIGN_TYPE);
alter table gradata.GRAS_ASSIGN_RULE_CHGS
  add constraint FK_GRAS_ASS_RUL_CH_NE_ASS_TY foreign key (NEXT_ASSIGN_TYPE)
  references GRARULE.GRAS_ASSIGN_TYPE(ASSIGN_TYPE);

create table gradata.GRAS_DEPT_SERVICER
(
  dept_no      VARCHAR2(20) not null,
  servicer_no  VARCHAR2(20) not null,
  is_valid     VARCHAR2(1) default 'Y' not null,
  pk_serial    VARCHAR2(10) not null,
  created_user VARCHAR2(100) not null,
  created_date DATE default SYSDATE not null,
  updated_user VARCHAR2(100) not null,
  updated_date DATE default SYSDATE not null
);
comment on table gradata.GRAS_DEPT_SERVICER
  is '部门对应服务人员设置';
comment on column gradata.GRAS_DEPT_SERVICER.dept_no
  is '部门代码';
comment on column gradata.GRAS_DEPT_SERVICER.servicer_no
  is '服务人员代码';
comment on column gradata.GRAS_DEPT_SERVICER.is_valid
  is '是否有效';
comment on column gradata.GRAS_DEPT_SERVICER.created_user
  is '录入人';
comment on column gradata.GRAS_DEPT_SERVICER.updated_user
  is '修改人';
comment on column gradata.GRAS_DEPT_SERVICER.created_date
  is '录入时间';
comment on column gradata.GRAS_DEPT_SERVICER.updated_date
  is '修改日期';
comment on column gradata.GRAS_DEPT_SERVICER.pk_serial
  is '数据主键';
alter table gradata.GRAS_DEPT_SERVICER
  add constraint PK_GRAS_DEPT_SERVICER primary key (PK_SERIAL);
alter table gradata.GRAS_DEPT_SERVICER
  add constraint FK_GRAS_DEPT_SERV_DEPT_NO foreign key (DEPT_NO)
  references SLISINTF.DEPARTMENT_INFO(DEPT_NO);
alter table gradata.GRAS_DEPT_SERVICER
  add constraint FK_GRAS_DEPT_SERV_SERV_NO foreign key (SERVICER_NO)
  references SLISINTF.STAFF_INFO(EMP_NO);
alter table gradata.GRAS_DEPT_SERVICER
  add constraint CK_GRAS_DEPT_SERV_IS_VAL
  check (IS_VALID in ('Y','N'));

create table gradata.GRAS_BRANCH_SERVICER
(
  branch_code  VARCHAR2(10) not null,
  servicer_no  VARCHAR2(20),
  is_valid     VARCHAR2(1) default 'Y' not null,
  pk_serial    VARCHAR2(10) not null,
  created_user VARCHAR2(100) not null,
  created_date DATE default SYSDATE not null,
  updated_user VARCHAR2(100) not null,
  updated_date DATE default SYSDATE not null
);
comment on table gradata.GRAS_BRANCH_SERVICER
  is '营销服务部对应服务人员设置';
comment on column gradata.GRAS_BRANCH_SERVICER.branch_code
  is '机构代码';
comment on column gradata.GRAS_BRANCH_SERVICER.servicer_no
  is '服务人员代码';
comment on column gradata.GRAS_BRANCH_SERVICER.is_valid
  is '是否有效';
comment on column gradata.GRAS_BRANCH_SERVICER.created_user
  is '录入人';
comment on column gradata.GRAS_BRANCH_SERVICER.created_date
  is '录入日期';
comment on column gradata.GRAS_BRANCH_SERVICER.updated_user
  is '修改人';
comment on column gradata.GRAS_BRANCH_SERVICER.updated_date
  is '修改日期';
comment on column gradata.GRAS_BRANCH_SERVICER.pk_serial
  is '数据主键';
alter table gradata.GRAS_BRANCH_SERVICER
  add constraint PK_GRAS_BRANCH_SERVICER primary key (PK_SERIAL);
alter table gradata.GRAS_BRANCH_SERVICER
  add constraint FK_GRAS_BRANCH_SERV_BRA_CO foreign key (BRANCH_CODE)
  references SLISINTF.BRANCH_INFO(BRANCH_CODE);
alter table gradata.GRAS_BRANCH_SERVICER
  add constraint FK_GRAS_BRANCH_SERV_DEPT_NO foreign key (EMP_NO)
  references SLISINTF.STAFF_INFO(EMP_NO);
alter table gradata.GRAS_BRANCH_SERVICER
  add constraint CK_GRAS_BRANCH_SERV_IS_VAL
  check (IS_VALID in ('Y','N'));

create table gradata.GRAS_POLICY_SERVICER_ASSIGN
(
  policy_no           VARCHAR2(20) not null,
  assign_service_type VARCHAR2(2) not null,
  servicer_no         VARCHAR2(20),
  begin_date          DATE not null,
  end_date            DATE,
  pk_serial           VARCHAR2(10) not null,
  created_user        VARCHAR2(100) not null,
  created_date        DATE default SYSDATE not null,
  updated_user        VARCHAR2(100) not null,
  updated_date        DATE default SYSDATE not null
);
comment on table gradata.GRAS_POLICY_SERVICER_ASSIGN
  is '保单服务人员指定表';
comment on column gradata.GRAS_POLICY_SERVICER_ASSIGN.policy_no
  is '保单号';
comment on column gradata.GRAS_POLICY_SERVICER_ASSIGN.assign_service_type
  is '指定服务类型';
comment on column gradata.GRAS_POLICY_SERVICER_ASSIGN.servicer_no
  is '指定服务人员';
comment on column gradata.GRAS_POLICY_SERVICER_ASSIGN.begin_date
  is '指定起始日期';
comment on column gradata.GRAS_POLICY_SERVICER_ASSIGN.end_date
  is '指定终止日期';
comment on column gradata.GRAS_POLICY_SERVICER_ASSIGN.pk_serial
  is '数字主键';
comment on column gradata.GRAS_POLICY_SERVICER_ASSIGN.created_user
  is '录入人 ';
comment on column gradata.GRAS_POLICY_SERVICER_ASSIGN.created_date
  is '录入日期';
comment on column gradata.GRAS_POLICY_SERVICER_ASSIGN.updated_user
  is '修改人';
comment on column gradata.GRAS_POLICY_SERVICER_ASSIGN.updated_date
  is '修改日期';
alter table gradata.GRAS_POLICY_SERVICER_ASSIGN
  add constraint PK_GRAS_POLICY_SERVICER_ASSIGN primary key (POLICY_NO,ASSIGN_SERVICE_TYPE,BEGIN_DATE);
alter table gradata.GRAS_POLICY_SERVICER_ASSIGN
  add constraint FK_GRAS_POL_SERV_ASS_SERV_TY foreign key (ASSIGN_SERVICE_TYPE)
  references GRARULE.GRAS_ASSIGN_SERVICE(ASSIGN_SERVICE_TYPE);
alter table gradata.GRAS_POLICY_SERVICER_ASSIGN
  add constraint FK_GRAS_POL_SERV_ASS_SERV_NO foreign key (SERVICER_NO)
  references SLISINTF.STAFF_INFO(SERVICER_NO);

create table gradata.GRAS_SYS_ROLE
(
  role_id      VARCHAR2(30) not null,
  role_name    VARCHAR2(50),
  description  VARCHAR2(100),
  pk_serial    VARCHAR2(10) not null,
  created_user VARCHAR2(100) not null,
  created_date DATE default SYSDATE not null,
  updated_user VARCHAR2(100) not null,
  updated_date DATE default SYSDATE not null
);
comment on table gradata.GRAS_SYS_ROLE
  is '角色表';
comment on column gradata.GRAS_SYS_ROLE.role_id
  is '角色ID';
comment on column gradata.GRAS_SYS_ROLE.role_name
  is '角色名称';
comment on column gradata.GRAS_SYS_ROLE.description
  is '备注';
comment on column gradata.GRAS_SYS_ROLE.pk_serial
  is '数字主键';
comment on column gradata.GRAS_SYS_ROLE.created_user
  is '录入人 ';
comment on column gradata.GRAS_SYS_ROLE.created_date
  is '录入日期';
comment on column gradata.GRAS_SYS_ROLE.updated_user
  is '修改人';
comment on column gradata.GRAS_SYS_ROLE.updated_date
  is '修改日期';
alter table gradata.GRAS_SYS_ROLE
  add constraint PK_GRAS_SYS_ROLE primary key (ROLE_ID);

create table gradata.GRAS_SYS_MENU
(
  menu_id        VARCHAR2(4) not null,
  menu_name      VARCHAR2(50) not null,
  menu_id_father VARCHAR2(4),
  menu_url       VARCHAR2(100) not null,
  pk_serial      VARCHAR2(10) not null,
  created_user   VARCHAR2(100) not null,
  created_date   DATE default SYSDATE not null,
  updated_user   VARCHAR2(100) not null,
  updated_date   DATE default SYSDATE not null
);
comment on table GRAS_SYS_MENU
  is '菜单表';
comment on column GRAS_SYS_MENU.menu_name
  is '权限名称';
comment on column GRAS_SYS_MENU.menu_id_father
  is '权限父ID';
comment on column GRAS_SYS_MENU.menu_url
  is '菜单路径，对应页面的菜单项';
comment on column GRAS_SYS_MENU.menu_id
  is '菜单id';
comment on column GRAS_SYS_MENU.pk_serial
  is '数字主键';
comment on column GRAS_SYS_MENU.created_user
  is '录入人 ';
comment on column GRAS_SYS_MENU.created_date
  is '录入日期';
comment on column GRAS_SYS_MENU.updated_user
  is '修改人';
comment on column GRAS_SYS_MENU.updated_date
  is '修改日期';
alter table gradata.GRAS_SYS_MENU
  add constraint PK_GRAS_SYS_MENU primary key (MENU_ID);

create table gradata.GRAS_SYS_ROLE_MENU
(
  role_id      VARCHAR2(30) not null,
  menu_id      VARCHAR2(4) not null,
  pk_serial    VARCHAR2(10) not null,
  created_user VARCHAR2(100) not null,
  created_date DATE default SYSDATE not null,
  updated_user VARCHAR2(100) not null,
  updated_date DATE default SYSDATE not null
);
comment on table gradata.GRAS_SYS_ROLE_MENU
  is '角色与菜单关系表';
comment on column gradata.GRAS_SYS_ROLE_MENU.role_id
  is '角色ID';
comment on column gradata.GRAS_SYS_ROLE_MENU.menu_id
  is '权限ID';
comment on column gradata.GRAS_SYS_ROLE.pk_serial
  is '数字主键';
comment on column gradata.GRAS_SYS_ROLE_MENU.created_user
  is '录入人 ';
comment on column gradata.GRAS_SYS_ROLE_MENU.created_date
  is '录入日期';
comment on column gradata.GRAS_SYS_ROLE_MENU.updated_user
  is '修改人';
comment on column gradata.GRAS_SYS_ROLE_MENU.updated_date
  is '修改日期';
alter table gradata.GRAS_SYS_ROLE_MENU
  add constraint PK_GRAS_SYS_ROLE_MENU primary key (PK_SERIAL);
alter table gradata.GRAS_SYS_ROLE_MENU
  add constraint FK_GRAS_SYS_RO_ME_RO_ID foreign key (ROLE_ID)
  references GRADATA.GRAS_SYS_ROLE(ROLE_ID);
alter table gradata.GRAS_SYS_ROLE_MENU
  add constraint FK_GRAS_SYS_RO_ME_ME_ID foreign key (MENU_ID)
  references GRADATA.GRAS_SYS_MENU(MENU_ID);

create table gradata.GRAS_SYS_DATA_TRANS
(
  target_table VARCHAR2(50) not null,
  target_id    VARCHAR2(10) not null,
  orcl_flag    VARCHAR2(1) default 'Y' not null,
  es_flag      VARCHAR2(1) default 'Y' not null,
  pk_serial    VARCHAR2(10) not null,
  created_user VARCHAR2(100) not null,
  created_date DATE default SYSDATE not null,
  updated_user VARCHAR2(100) not null,
  updated_date DATE default SYSDATE not null
);
comment on table gradata.GRAS_SYS_DATA_TRANS
  is '数据传输记录';
comment on column gradata.GRAS_SYS_DATA_TRANS.target_table
  is '数据所在表名';
comment on column gradata.GRAS_SYS_DATA_TRANS.target_id
  is '数据对应的ID';
comment on column gradata.GRAS_SYS_DATA_TRANS.orcl_flag
  is 'ORACLE数据库传输正常标示，指示接口传输是否正常';
comment on column gradata.GRAS_SYS_DATA_TRANS.es_flag
  is 'ES数据库传输正常标示，
指示数据同步是否正常';
comment on column gradata.GRAS_SYS_DATA_TRANS.pk_serial
  is '数字主键';
comment on column gradata.GRAS_SYS_DATA_TRANS.created_user
  is '录入人 ';
comment on column gradata.GRAS_SYS_DATA_TRANS.created_date
  is '录入日期';
comment on column gradata.GRAS_SYS_DATA_TRANS.updated_user
  is '修改人';
comment on column gradata.GRAS_SYS_DATA_TRANS.updated_date
  is '修改日期';
alter table gradata.GRAS_SYS_DATA_TRANS
  add constraint PK_GRAS_SYS_DATA_TRANS primary key (PK_SERIAL);
alter table gradata.GRAS_SYS_DATA_TRANS
  add constraint CK_GRAS_SYS_DATA_TRANS_ORCL
  check (ORCL_FLAG in ('Y','N'));
alter table gradata.GRAS_SYS_DATA_TRANS
  add constraint CK_GRAS_SYS_DATA_TRANS_ES
  check (ES_FLAG in ('Y','N'));
  
  
create table gradata.GRAS_BATCH_PLAN
(
  sequence             VARCHAR2(10) not null,
  proc_code            VARCHAR2(4),
  branch_code          VARCHAR2(10),
  parameters           VARCHAR2(2000),
  scheduled_start_date DATE,
  priority             NUMBER,
  proc_flag            VARCHAR2(2),
  fcu                  VARCHAR2(100),
  fcd                  DATE,
  lcu                  VARCHAR2(100),
  lcd                  DATE,
  job_no               NUMBER,
  job_source_module    VARCHAR2(4),
  start_date           DATE,
  end_date             DATE,
  created_user         VARCHAR2(100),
  created_date         DATE default SYSDATE,
  updated_user         VARCHAR2(100),
  updated_date         DATE default SYSDATE
);

comment on table gradata.GRAS_BATCH_PLAN
  is '批次任务执行表';
comment on column gradata.GRAS_BATCH_PLAN.sequence
  is '流水号';
comment on column gradata.GRAS_BATCH_PLAN.proc_code
  is '批处理代码';
comment on column gradata.GRAS_BATCH_PLAN.branch_code
  is '机构代码';
comment on column gradata.GRAS_BATCH_PLAN.parameters
  is '批处理参数';
comment on column gradata.GRAS_BATCH_PLAN.scheduled_start_date
  is '任务计划启动时间';
comment on column gradata.GRAS_BATCH_PLAN.priority
  is '优先级';
comment on column gradata.GRAS_BATCH_PLAN.proc_flag
  is '批处理状态';
comment on column gradata.GRAS_BATCH_PLAN.fcu
  is '创建人';
comment on column gradata.GRAS_BATCH_PLAN.fcd
  is '创建日期';
comment on column gradata.GRAS_BATCH_PLAN.lcu
  is '修改人';
comment on column gradata.GRAS_BATCH_PLAN.lcd
  is '创建日期';
comment on column gradata.GRAS_BATCH_PLAN.job_no
  is 'job号';
comment on column gradata.GRAS_BATCH_PLAN.job_source_module
  is 'job模块号';
comment on column gradata.GRAS_BATCH_PLAN.start_date
  is '任务起始时间';
comment on column gradata.GRAS_BATCH_PLAN.end_date
  is '任务结束时间(不精确,间隔时间越大差异越大)';
comment on column gradata.GRAS_BATCH_PLAN.created_user
  is '录入人';
comment on column gradata.GRAS_BATCH_PLAN.created_date
  is '录入日期';
comment on column gradata.GRAS_BATCH_PLAN.updated_user
  is '修改人 ';
comment on column gradata.GRAS_BATCH_PLAN.updated_date
  is '修改日期';

alter table GRADATA.GRAS_BATCH_PLAN
  add constraint PK_GRAS_BATCH_PLAN primary key (SEQUENCE);

  
create table GRADATA.GRAS_ASSIGN_BATCH_RECORD
(
  branch_code  VARCHAR2(10) not null,
  due_month    DATE not null,
  batch_code   VARCHAR2(4) not null,
  batch_seq    VARCHAR2(10),
  finish_flag  VARCHAR2(1),
  pk_serial    VARCHAR2(10) not null,
  created_user VARCHAR2(100) not null,
  created_date DATE default SYSDATE not null,
  updated_user VARCHAR2(100) not null,
  updated_date DATE default SYSDATE not null
);

comment on table GRADATA.GRAS_ASSIGN_BATCH_RECORD
  is '机构分单批次申请记录表';
comment on column GRADATA.GRAS_ASSIGN_BATCH_RECORD.branch_code
  is '机构代码';
comment on column GRADATA.GRAS_ASSIGN_BATCH_RECORD.due_month
  is '应缴月';
comment on column GRADATA.GRAS_ASSIGN_BATCH_RECORD.batch_code
  is '批处理代码';
comment on column GRADATA.GRAS_ASSIGN_BATCH_RECORD.batch_seq
  is '批处理流水';
comment on column GRADATA.GRAS_ASSIGN_BATCH_RECORD.finish_flag
  is '完成状态';
comment on column GRADATA.GRAS_ASSIGN_BATCH_RECORD.pk_serial
  is '数据主键';
comment on column GRADATA.GRAS_ASSIGN_BATCH_RECORD.created_user
  is '录入人';
comment on column GRADATA.GRAS_ASSIGN_BATCH_RECORD.created_date
  is '录入日期';
comment on column GRADATA.GRAS_ASSIGN_BATCH_RECORD.updated_user
  is '修改人';
comment on column GRADATA.GRAS_ASSIGN_BATCH_RECORD.updated_date
  is '修改日期';

alter table gradata.GRAS_ASSIGN_BATCH_RECORD
add constraint PK_GRAS_ASSIGN_BATCH_RECORD primary key (BRANCH_CODE,DUE_MONTH,BATCH_CODE);
alter table gradata.GRAS_ASSIGN_BATCH_RECORD
  add constraint FK_GRAS_ASS_BA_REC_BR_CO foreign key (BRANCH_CODE)
  references SLISINTF.BRANCH_INFO(BRANCH_CODE);  
  
create table gradata.GRAS_BATCH_ERROR_RECORD
(
  sequence       VARCHAR2(10) not null,
  proc_code      VARCHAR2(4) not null,
  ctrl_no        VARCHAR2(40),
  ora_error_code NUMBER(6),
  error_message  VARCHAR2(300),
  operating_user VARCHAR2(100),
  operating_date DATE,
  sid            NUMBER,
  serial         NUMBER,
  branch_code    VARCHAR2(10)
);
-- Add comments to the table 
comment on table GRADATA.GRAS_BATCH_ERROR_RECORD
  is '系统批处理出错信息表';
-- Add comments to the columns 
comment on column GRADATA.GRAS_BATCH_ERROR_RECORD.sequence
  is '流水号-用于对应执行信息表数据';
comment on column GRADATA.GRAS_BATCH_ERROR_RECORD.proc_code
  is '批处理代码';
comment on column GRADATA.GRAS_BATCH_ERROR_RECORD.ctrl_no
  is '出错控制号';
comment on column GRADATA.GRAS_BATCH_ERROR_RECORD.ora_error_code
  is 'ORA出错代码';
comment on column GRADATA.GRAS_BATCH_ERROR_RECORD.error_message
  is '出错信息';
comment on column GRADATA.GRAS_BATCH_ERROR_RECORD.operating_user
  is '操作执行用户';
comment on column GRADATA.GRAS_BATCH_ERROR_RECORD.operating_date
  is '操作执行时间';
comment on column GRADATA.GRAS_BATCH_ERROR_RECORD.sid
  is '运行该JOB的SID';
comment on column GRADATA.GRAS_BATCH_ERROR_RECORD.serial
  is 'SID对应的SERIAL#';
comment on column GRADATA.GRAS_BATCH_ERROR_RECORD.branch_code
  is '机构代码';
alter table GRADATA.GRAS_BATCH_ERROR_RECORD
  add constraint PK_GRAS_BATCH_ERROR_RECORD primary key (SEQUENCE);  
  
  
  
create table GRADATA.GRAS_ASSIGN_FAILED_RECORD
(
  operate_type        VARCHAR2(1) not null,
  policy_no           VARCHAR2(20) not null,
  failed_type         VARCHAR2(2) not null,
  batch_seq           VARCHAR2(10),
  pk_serial           VARCHAR2(10) not null,
  created_user        VARCHAR2(100) not null,
  created_date        DATE default SYSDATE not null,
  updated_user        VARCHAR2(100) not null,
  updated_date        DATE default SYSDATE not null
);
comment on table gradata.GRAS_ASSIGN_FAILED_RECORD
  is '分单失败保单记录表';
comment on column gradata.GRAS_ASSIGN_FAILED_RECORD.operate_type
  is '操作类型：1-手动；0-自动';
comment on column gradata.GRAS_ASSIGN_FAILED_RECORD.policy_no
  is '保单号';
comment on column gradata.GRAS_ASSIGN_FAILED_RECORD.failed_type
  is '分单失败类型';
comment on column gradata.GRAS_ASSIGN_FAILED_RECORD.batch_seq
  is '批处理流水号';
comment on column gradata.GRAS_ASSIGN_FAILED_RECORD.pk_serial
  is '数字主键';
comment on column gradata.GRAS_ASSIGN_FAILED_RECORD.created_user
  is '录入人 ';
comment on column gradata.GRAS_ASSIGN_FAILED_RECORD.created_date
  is '录入日期';
comment on column gradata.GRAS_ASSIGN_FAILED_RECORD.updated_user
  is '修改人';
comment on column gradata.GRAS_ASSIGN_FAILED_RECORD.updated_date
  is '修改日期';
alter table gradata.GRAS_ASSIGN_FAILED_RECORD
  add constraint PK_GRAS_ASSIGN_FAILED_RECORD primary key (PK_SERIAL);
alter table gradata.GRAS_ASSIGN_FAILED_RECORD
  add constraint FK_GRAS_ASS_FAIL_REC_POL_NO foreign key (POLICY_NO)
  references SLISINTF.POLICY(POLICY_NO);
alter table gradata.GRAS_ASSIGN_FAILED_RECORD
  add constraint FK_GRAS_ASS_FAIL_REC_FAIL_TY foreign key (FAILED_TYPE)
  references GRARULE.GRAS_ASSIGN_FAILED_TYPE(FAILED_TYPE);

create public synonym GRAS_BRANCH_ASSIGN_RULE               for gradata.GRAS_BRANCH_ASSIGN_RULE;
create public synonym GRAS_AREA_STAFF                       for gradata.GRAS_AREA_STAFF;
create public synonym GRAS_POLICY_GEO                       for gradata.GRAS_POLICY_GEO;
create public synonym GRAS_BRANCH_GEO                       for gradata.GRAS_BRANCH_GEO;
create public synonym GRAS_BANK_GEO                         for gradata.GRAS_BANK_GEO;
create public synonym GRAS_BANK_EMP                         for gradata.GRAS_BANK_EMP;
create public synonym GRAS_BRANCH_STYLE                     for gradata.GRAS_BRANCH_STYLE;
create public synonym GRAS_ASSIGN_RULE_CHGS                 for gradata.GRAS_ASSIGN_RULE_CHGS;
create public synonym GRAS_DEPT_SERVICER                    for gradata.GRAS_DEPT_SERVICER;
create public synonym GRAS_BRANCH_SERVICER                  for gradata.GRAS_BRANCH_SERVICER;
create public synonym GRAS_POLICY_SERVICER_ASSIGN           for gradata.GRAS_POLICY_SERVICER_ASSIGN;
create public synonym GRAS_SYS_ROLE                         for gradata.GRAS_SYS_ROLE;
create public synonym GRAS_SYS_ROLE_MENU                    for gradata.GRAS_SYS_ROLE_MENU;
create public synonym GRAS_SYS_MENU                         for gradata.GRAS_SYS_MENU;
create public synonym GRAS_SYS_DATA_TRANS                   for gradata.GRAS_SYS_DATA_TRANS;
create public synonym GRAS_ASSIGN_FAILED_RECORD             for gradata.GRAS_ASSIGN_FAILED_RECORD;
create public synonym GRAS_BATCH_PLAN            			for gradata.GRAS_BATCH_PLAN;
create public synonym GRAS_ASSIGN_BATCH_RECORD            	for gradata.GRAS_ASSIGN_BATCH_RECORD;
create public synonym GRAS_BATCH_ERROR_RECORD            	for gradata.GRAS_BATCH_ERROR_RECORD;



grant select,insert,update on gradata.GRAS_BRANCH_ASSIGN_RULE      to gracde,graopr;
grant select,insert,update on gradata.GRAS_AREA_STAFF              to gracde,graopr;
grant select,insert,update on gradata.GRAS_POLICY_GEO              to gracde,graopr;
grant select,insert,update on gradata.GRAS_BRANCH_GEO              to gracde,graopr;
grant select,insert,update on gradata.GRAS_BANK_GEO                to gracde,graopr;
grant select,insert,update on gradata.GRAS_BANK_EMP                to gracde,graopr;
grant select,insert,update on gradata.GRAS_BRANCH_STYLE            to gracde,graopr;
grant select,insert,update on gradata.GRAS_ASSIGN_RULE_CHGS        to gracde,graopr;
grant select,insert,update on gradata.GRAS_DEPT_SERVICER           to gracde,graopr;
grant select,insert,update on gradata.GRAS_BRANCH_SERVICER         to gracde,graopr;
grant select,insert,update on gradata.GRAS_POLICY_SERVICER_ASSIGN  to gracde,graopr;
grant select,insert,update on gradata.GRAS_SYS_ROLE                to gracde,graopr;
grant select,insert,update on gradata.GRAS_SYS_ROLE_MENU           to gracde,graopr;
grant select,insert,update on gradata.GRAS_SYS_MENU                to gracde,graopr;
grant select,insert,update on gradata.GRAS_SYS_DATA_TRANS          to gracde,graopr;
grant select,insert,update on gradata.GRAS_ASSIGN_FAILED_RECORD    to gracde,graopr;
grant select,insert,update on gradata.GRAS_BATCH_PLAN    		   to gracde,graopr;
grant select,insert,update on gradata.GRAS_ASSIGN_BATCH_RECORD     to gracde,graopr;
grant select,insert,update on gradata.GRAS_BATCH_ERROR_RECORD      to gracde,graopr;