jn0801.sql

自己制作的联通CRM,支持客户分类,管理,升级,积分管理等等..

insert into powerinfo(nid,sname,npid,slink) values(27,'集团客户分配',3,'/allotCompany.do?tag=allotcompany');
insert into powerinfo(nid,sname,npid,slink) values(28,'集团客转正',3,'allotCompany.do?tag=potentialcompany');
insert into powerinfo(nid,sname,npid,slink) values(29,'集团客户审核',3,'/latencyGroup.do?task=checkupLatency');
insert into powerinfo(nid,sname,npid,slink) values(30,'集团联系人管理',3,'/linkmanInfoManage.do?tag=enterPageInfo');	
insert into powerinfo(nid,sname,npid,slink) values(31,'集团视图查看',3,'/companyViewAction.do?tag=list');
insert into powerinfo(nid,sname,npid,slink) values(32,'积分兑换记录',4,'/consumeRecord.do?task=showConsumeRecord');


commit;

--创建话费表
create table phonefeeinfo (
   phonefeeinfoid 	  NUMBER not null,
   userid             NUMBER                          not null,
   mdate              VARCHAR2(200),
   money              NUMBER,
   prepay			  NUMBER,
   constraint PK_PHONEFEEINFO primary key (phonefeeinfoid)
);

comment on table phonefeeinfo is
'话费表';

comment on column phonefeeinfo.phonefeeinfoid is
'话费编号';

comment on column phonefeeinfo.userid is
'用户编号';

comment on column phonefeeinfo.mdate is
'日期';

comment on column phonefeeinfo.money is
'费用';

comment on column phonefeeinfo.prepay is
'预存话费';

create sequence seq_phonefeeinfo
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1;

--业务类型表

create table operationtype  (
   typeid             number                          not null,
   typename           VARCHAR2(1000),
   npid               number,
   nlink              VARCHAR2(100),
   constraint PK_OPERATIONTYPE primary key (typeid)
);

comment on table operationtype is
'业务类型表';

comment on column operationtype.typeid is
'类别ID号';

comment on column operationtype.typename is
'类别名称';

comment on column operationtype.npid is
'父类别ID号';

comment on column operationtype.nlink is
'功能链接';



create sequence seq_operationtype
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'即时业务',0);	
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'非即时业务',0);	
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'来电显示',1);	
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'主叫隐藏',1);	
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'呼叫转移',1);	
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'立即开机',1);	
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'临时授信',1);	
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'申挂停机',1);	
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'撤消申挂停机',1);	
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'国内长途',2);
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'国际长途',2);
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'增值业务',2);
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'客户化优惠',2);
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'客户查询信息变更',2);
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'邮寄服务',2);
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'备卡激活',2);	
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'代办国际漫游',2);	
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'套餐变更',2);	
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'客户经理上门服务预约',2);	
insert into operationtype(typeid,typename,npid) values(seq_operationtype.nextval,'客户经理电话服务预约',2);

commit;

--业务受理登记表

create table acceptoperation  (
   nid                number                          not null,
   nmax               VARCHAR2(100),
   userid             number,
   nmin               VARCHAR2(100),
   emnid              number,
   accepttime         VARCHAR2(100),
   dredgestate        VARCHAR2(100),
   inuremoney         VARCHAR2(100),
   usermob            number,
   overdraft          number,
   userfromtel	      number,
   transfertel	      number,
   noverdraft         NUMBER,
   causation          VARCHAR2(100),
   remark             VARCHAR2(3000),
   incrementid        VARCHAR2(1000),
   postaddress        VARCHAR2(1000),
   tel                VARCHAR2(100),
   sname              VARCHAR2(100),
   address            VARCHAR2(1000),
   sid                VARCHAR2(100),
   availability       VARCHAR2(100),
   postalcode         NUMBER,
   bespeaktime        VARCHAR2(100),
   bespeakaddress     VARCHAR2(1000),
   bespeakname        VARCHAR2(100),
   bespeaktel         VARCHAR2(100),
   bespeakremark      VARCHAR2(3000),
   bespeakid          VARCHAR2(1000),
   postservicetype	  VARCHAR2(100),
   dealmark			  VARCHAR2(1000)             default '0',
   lastdealer		  VARCHAR2(1000),
   oldaddress         VARCHAR2(1000), 
   oldpostalcode	  NUMBER,
   oldtel             VARCHAR2(100),
   mschangetype       VARCHAR2(1000),
   constraint PK_ACCEPTOPERATION primary key (nid)
);

comment on table acceptoperation is
'业务受理登记表';

comment on column acceptoperation.nid is
'受理登记表编号';

comment on column acceptoperation.nmax is
'业务类型名称';

comment on column acceptoperation.userid is
'用户ID号';

comment on column acceptoperation.postservicetype is
'邮寄服务类型';

comment on column acceptoperation.nmin is
'所属业务类型';

comment on column acceptoperation.emnid is
'登记者工号(ID号)';

comment on column acceptoperation.accepttime is
'受理时间';

comment on column acceptoperation.dredgestate is
'开通状态';

comment on column acceptoperation.inuremoney is
'生效账期';

comment on column acceptoperation.usermob is
'用户号码';

comment on column acceptoperation.userfromtel is
'用户来电号码';	

comment on column acceptoperation.transfertel is
'用户呼叫转移号码';	 

comment on column acceptoperation.overdraft is
'可授信金额';

comment on column acceptoperation.noverdraft is
'实际授信金额';

comment on column acceptoperation.causation is
'授信原因';

comment on column acceptoperation.remark is
'备注';

comment on column acceptoperation.incrementid is
'增值业务编号';

comment on column acceptoperation.postaddress is
'邮寄地址';

comment on column acceptoperation.tel is
'联络电话';

comment on column acceptoperation.sname is
'用户姓名';

comment on column acceptoperation.address is
'用户地址';

comment on column acceptoperation.sid is
'用户有效证件号';

comment on column acceptoperation.availability is
'证件有效期';

comment on column acceptoperation.postalcode is
'邮编';

comment on column acceptoperation.bespeaktime is
'预约上门时间';

comment on column acceptoperation.bespeakaddress is
'预约上门地点';

comment on column acceptoperation.bespeakname is
'预约上门联系人';

comment on column acceptoperation.bespeaktel is
'预约上门联系电话';

comment on column acceptoperation.bespeakremark is
'预约上门联系备注';

comment on column acceptoperation.bespeakid is
'预约上门服务类型编号';

comment on column acceptoperation.dealmark is
'处理的标志';

comment on column acceptoperation.lastdealer is
'最终处理人';

comment on column acceptoperation.oldaddress is
'用户旧地址';

comment on column acceptoperation.oldpostalcode is
'用户旧邮编';

comment on column acceptoperation.oldtel is
'用户旧联系电话';

comment on column acceptoperation.mschangetype is
'查询信息变更类型';

create sequence seq_acceptoperation
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

commit;

--增值业务表

create table T_increment  (
   nid                number                          not null,
   incname            VARCHAR2(300),
   npid               number,
   constraint PK_INCREMENT primary key (nid)
);

comment on table T_increment is
'增值业务表';

comment on column T_increment.nid is
'类别ID号';

comment on column T_increment.incname is
'增值业务名称';

comment on column T_increment.npid is
'父类别ID号';


create sequence seq_T_increment
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;


insert into T_increment(nid,incname,npid) values(seq_T_increment.nextval,'联通秘书',0);
insert into T_increment(nid,incname,npid) values(seq_T_increment.nextval,'炫铃',0);	
insert into T_increment(nid,incname,npid) values(seq_T_increment.nextval,'话费周周报',0);	
insert into T_increment(nid,incname,npid) values(seq_T_increment.nextval,'如意信箱',0);	
insert into T_increment(nid,incname,npid) values(seq_T_increment.nextval,'随手看',0);	
insert into T_increment(nid,incname,npid) values(seq_T_increment.nextval,'新闻',5);	
insert into T_increment(nid,incname,npid) values(seq_T_increment.nextval,'笑话',5);	
insert into T_increment(nid,incname,npid) values(seq_T_increment.nextval,'健康生活贴士',5);	
insert into T_increment(nid,incname,npid) values(seq_T_increment.nextval,'天气预报',5);	
insert into T_increment(nid,incname,npid) values(seq_T_increment.nextval,'8001短信',0);		

commit;




--创建业务受理处理结果 记录表

create table acceptorresult  (
   nid                NUMBER                          not null,
   gnid				  NUMBER,
   acceptid           NUMBER,
   dealerid           NUMBER,
   dtime              VARCHAR2(200),
   dresult            VARCHAR2(1000),
   dmark              VARCHAR2(100),
   constraint PK_ACCEPTORRESULT primary key (nid)
);

comment on column acceptorresult.nid is
'结果ID号';

comment on column acceptorresult.gnid is
'工单序号';

comment on column acceptorresult.acceptid is
'受理ID号';

comment on column acceptorresult.dealerid is
'处理者工号';

comment on column acceptorresult.dtime is
'处理者时间';

comment on column acceptorresult.dresult is
'处理结果';

comment on column acceptorresult.dmark is
'处理标记';


create sequence seq_acceptorresult
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

commit;