楼主去年写的存储过程，前面是建表语句：想看存储过程，可以直接CTRL+F procedurecreate table OA_GG ( GGID INTEGER not null, LMID INTEGER, HEADER VARCHAR2(200), CONTENT CLOB, FBR VARCHAR2(100), FBDATE DATE, SAVEDATE DATE, ISTOP INTEGER, GGFW VARCHAR2(4000), GGFWNAME VARCHAR2(4000), KEEPDATE DATE, JJGG INTEGER, SFVIDEO INTEGER, VIDEOURL VARCHAR2(500), FBREMPID VARCHAR2(100), FBREMPNAME VARCHAR2(100), ISCALLSIGN INTEGER default 0, ISREADSIGN INTEGER default 0, TPSM VARCHAR2(150), CLICKTIMES NUMBER(6), TOTALCOMMENTS NUMBER(6) ) ; alter table OA_GG add constraint PK_OA_GG_TEMP primary key (GGID); create table OA_GG_FOR_DS ( GGID INTEGER ) ; create table QRTZ_JOB_DETAILS ( JOB_NAME VARCHAR2(200) not null, JOB_GROUP VARCHAR2(200) not null, DESCRIPTION VARCHAR2(250), JOB_CLASS_NAME VARCHAR2(250) not null, IS_DURABLE VARCHAR2(1) not null, IS_VOLATILE VARCHAR2(1) not null, IS_STATEFUL VARCHAR2(1) not null, REQUESTS_RECOVERY VARCHAR2(1) not null, JOB_DATA BLOB ) ; comment on table QRTZ_JOB_DETAILS is '存储每一个已配置的Job的详细信息'; alter table QRTZ_JOB_DETAILS add primary key (JOB_NAME, JOB_GROUP); create index IDX_QRTZ_J_REQ_RECOVERY on QRTZ_JOB_DETAILS (REQUESTS_RECOVERY); create table QRTZ_TRIGGERS ( TRIGGER_NAME VARCHAR2(200) not null, TRIGGER_GROUP VARCHAR2(200) not null, JOB_NAME VARCHAR2(200) not null, JOB_GROUP VARCHAR2(200) not null, IS_VOLATILE VARCHAR2(1) not null, DESCRIPTION VARCHAR2(250), NEXT_FIRE_TIME NUMBER(13), PREV_FIRE_TIME NUMBER(13), PRIORITY NUMBER(13), TRIGGER_STATE VARCHAR2(16) not null, TRIGGER_TYPE VARCHAR2(8) not null, START_TIME NUMBER(13) not null, END_TIME NUMBER(13), CALENDAR_NAME VARCHAR2(200), MISFIRE_INSTR NUMBER(2), JOB_DATA BLOB ) ; comment on table QRTZ_TRIGGERS is '存储已配置的Trigger的信息'; alter table QRTZ_TRIGGERS add primary key (TRIGGER_NAME, TRIGGER_GROUP); alter table QRTZ_TRIGGERS add foreign key (JOB_NAME, JOB_GROUP) references QRTZ_JOB_DETAILS (JOB_NAME, JOB_GROUP); create index IDX_QRTZ_T_NEXT_FIRE_TIME on QRTZ_TRIGGERS (NEXT_FIRE_TIME); create index IDX_QRTZ_T_NFT_ST on QRTZ_TRIGGERS (NEXT_FIRE_TIME, TRIGGER_STATE); create index IDX_QRTZ_T_STATE on QRTZ_TRIGGERS (TRIGGER_STATE); create index IDX_QRTZ_T_VOLATILE on QRTZ_TRIGGERS (IS_VOLATILE); create table QRTZ_BLOB_TRIGGERS ( TRIGGER_NAME VARCHAR2(200) not null, TRIGGER_GROUP VARCHAR2(200) not null, BLOB_DATA BLOB ) ; comment on table QRTZ_BLOB_TRIGGERS is 'Trigger作为Blob类型存储'; alter table QRTZ_BLOB_TRIGGERS add primary key (TRIGGER_NAME, TRIGGER_GROUP); alter table QRTZ_BLOB_TRIGGERS add foreign key (TRIGGER_NAME, TRIGGER_GROUP) references QRTZ_TRIGGERS (TRIGGER_NAME, TRIGGER_GROUP); create table QRTZ_CALENDARS ( CALENDAR_NAME VARCHAR2(200) not null, CALENDAR BLOB not null ) ; comment on table QRTZ_CALENDARS is '存储Quartz的Calendar信息'; alter table QRTZ_CALENDARS add primary key (CALENDAR_NAME); create table QRTZ_CRON_TRIGGERS ( TRIGGER_NAME VARCHAR2(200) not null, TRIGGER_GROUP VARCHAR2(200) not null, CRON_EXPRESSION VARCHAR2(120) not null, TIME_ZONE_ID VARCHAR2(80) ) ; comment on table QRTZ_CRON_TRIGGERS is '存储CronTrigger，包括Cron表达式和时区信息'; alter table QRTZ_CRON_TRIGGERS add primary key (TRIGGER_NAME, TRIGGER_GROUP); alter table QRTZ_CRON_TRIGGERS add foreign key (TRIGGER_NAME, TRIGGER_GROUP) references QRTZ_TRIGGERS (TRIGGER_NAME, TRIGGER_GROUP); create table QRTZ_FIRED_TRIGGERS ( ENTRY_ID VARCHAR2(95) not null, TRIGGER_NAME VARCHAR2(200) not null, TRIGGER_GROUP VARCHAR2(200) not null, IS_VOLATILE VARCHAR2(1) not null, INSTANCE_NAME VARCHAR2(200) not null, FIRED_TIME NUMBER(13) not null, PRIORITY NUMBER(13) not null, STATE VARCHAR2(16) not null, JOB_NAME VARCHAR2(200), JOB_GROUP VARCHAR2(200), IS_STATEFUL VARCHAR2(1), REQUESTS_RECOVERY VARCHAR2(1) ) ; comment on table QRTZ_FIRED_TRIGGERS is '存储与已触发的Trigger相关的状态信息，以及相联Job的执行信息'; alter table QRTZ_FIRED_TRIGGERS add primary key (ENTRY_ID); create index IDX_QRTZ_FT_JOB_GROUP on QRTZ_FIRED_TRIGGERS (JOB_GROUP); create index IDX_QRTZ_FT_JOB_NAME on QRTZ_FIRED_TRIGGERS (JOB_NAME); create index IDX_QRTZ_FT_JOB_REQ_RECOVERY on QRTZ_FIRED_TRIGGERS (REQUESTS_RECOVERY); create index IDX_QRTZ_FT_JOB_STATEFUL on QRTZ_FIRED_TRIGGERS (IS_STATEFUL); create index IDX_QRTZ_FT_TRIG_GROUP on QRTZ_FIRED_TRIGGERS (TRIGGER_GROUP); create index IDX_QRTZ_FT_TRIG_INST_NAME on QRTZ_FIRED_TRIGGERS (INSTANCE_NAME); create index IDX_QRTZ_FT_TRIG_NAME on QRTZ_FIRED_TRIGGERS (TRIGGER_NAME); create index IDX_QRTZ_FT_TRIG_NM_GP on QRTZ_FIRED_TRIGGERS (TRIGGER_NAME, TRIGGER_GROUP); create index IDX_QRTZ_FT_TRIG_VOLATILE on QRTZ_FIRED_TRIGGERS (IS_VOLATILE); create table QRTZ_JOB_LISTENERS ( JOB_NAME VARCHAR2(200) not null, JOB_GROUP VARCHAR2(200) not null, JOB_LISTENER VARCHAR2(200) not null ) ; comment on table QRTZ_JOB_LISTENERS is '存储有关已配置的JobListener的信息'; alter table QRTZ_JOB_LISTENERS add primary key (JOB_NAME, JOB_GROUP, JOB_LISTENER); alter table QRTZ_JOB_LISTENERS add foreign key (JOB_NAME, JOB_GROUP) references QRTZ_JOB_DETAILS (JOB_NAME, JOB_GROUP); create table QRTZ_JOB_LOGGINGS ( ID VARCHAR2(50) not null, INSTANCE_ID VARCHAR2(50), TRIGGER_GROUP VARCHAR2(50), TRIGGER_NAME VARCHAR2(50), JOB_GROUP VARCHAR2(50), JOB_NAME VARCHAR2(50), FIRED_TIME DATE, JOB_ACTION VARCHAR2(20), ERROR_MESSAGE VARCHAR2(2000), FLOW_UUID VARCHAR2(36), START_TIME TIMESTAMP(6), END_TIME TIMESTAMP(6) ) ; alter table QRTZ_JOB_LOGGINGS add constraint PRIMARYQRTZ_JOB_LOGGINGS1 primary key (ID); create table QRTZ_JOB_PLANNINGS ( INSTANCE_ID VARCHAR2(50) not null, SCOPE_TYPE NUMBER not null, SCOPE_NAME VARCHAR2(50) not null, ACCESS_RULE NUMBER not null ) ; alter table QRTZ_JOB_PLANNINGS add constraint PRIMARYQRTZ_JOB_PLANNINGS1 primary key (INSTANCE_ID, SCOPE_TYPE, SCOPE_NAME, ACCESS_RULE); create table QRTZ_JOB_SCHEDULES ( ID VARCHAR2(50) not null, TRIGGER_GROUP VARCHAR2(50), TRIGGER_NAME VARCHAR2(50), JOB_GROUP VARCHAR2(50), JOB_NAME VARCHAR2(50), DESCRIPTION VARCHAR2(200), TRIGGER_TYPE NUMBER, TRIGGER_EXPRESSION VARCHAR2(30), JOB_CLASS VARCHAR2(256), JOB_DATA VARCHAR2(2000), ACTIVE INTEGER default 1, UPDATE_TIME TIMESTAMP(6) default SYSDATE ) ; alter table QRTZ_JOB_SCHEDULES add constraint PRIMARYQRTZ_JOB_SCHEDULES1 primary key (ID); create table QRTZ_LOCKS ( LOCK_NAME VARCHAR2(40) not null ) ; comment on table QRTZ_LOCKS is '存储程序的悲观锁的信息'; alter table QRTZ_LOCKS add primary key (LOCK_NAME); create table QRTZ_PAUSED_TRIGGER_GRPS ( TRIGGER_GROUP VARCHAR2(200) not null ) ; comment on table QRTZ_PAUSED_TRIGGER_GRPS is '存储已暂停的Trigger组的信息'; alter table QRTZ_PAUSED_TRIGGER_GRPS add primary key (TRIGGER_GROUP); create table QRTZ_SCHEDULER_STATE ( INSTANCE_NAME VARCHAR2(200) not null, LAST_CHECKIN_TIME NUMBER(13) not null, CHECKIN_INTERVAL NUMBER(13) not null ) ; comment on table QRTZ_SCHEDULER_STATE is '存储少量的有关Scheduler的状态信息，和别的Scheduler实例'; alter table QRTZ_SCHEDULER_STATE add primary key (INSTANCE_NAME); create table QRTZ_SIMPLE_TRIGGERS ( TRIGGER_NAME VARCHAR2(200) not null, TRIGGER_GROUP VARCHAR2(200) not null, REPEAT_COUNT NUMBER(7) not null, REPEAT_INTERVAL NUMBER(12) not null, TIMES_TRIGGERED NUMBER(10) not null ) ; comment on table QRTZ_SIMPLE_TRIGGERS is '存储简单的Trigger，包括重复次数、间隔、以及已触的次数'; alter table QRTZ_SIMPLE_TRIGGERS add primary key (TRIGGER_NAME, TRIGGER_GROUP); alter table QRTZ_SIMPLE_TRIGGERS add foreign key (TRIGGER_NAME, TRIGGER_GROUP) references QRTZ_TRIGGERS (TRIGGER_NAME, TRIGGER_GROUP); create table QRTZ_TRIGGER_LISTENERS ( TRIGGER_NAME VARCHAR2(200) not null, TRIGGER_GROUP VARCHAR2(200) not null, TRIGGER_LISTENER VARCHAR2(200) not null ) ; comment on table QRTZ_TRIGGER_LISTENERS is '存储已配置的TriggerListener的信息'; alter table QRTZ_TRIGGER_LISTENERS add primary key (TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_LISTENER); alter table QRTZ_TRIGGER_LISTENERS add foreign key (TRIGGER_NAME, TRIGGER_GROUP) references QRTZ_TRIGGERS (TRIGGER_NAME, TRIGGER_GROUP); create table T_AUTH_FUNCTION ( ID VARCHAR2(50) not null, FUNCTION_CODE VARCHAR2(64) not null, FUNCTION_NAME VARCHAR2(64), URI VARCHAR2(512), FUNCTION_LEVEL VARCHAR2(8), PARENT_CODE VARCHAR2(64), ACTIVE VARCHAR2(8), DISPLAY_ORDER VARCHAR2(8), CHECKABLE VARCHAR2(8), FUNCTION_TYPE VARCHAR2(16), LEAF VARCHAR2(8), ICON_CLS VARCHAR2(64), CLS VARCHAR2(64), FUNCTION_DESC VARCHAR2(256), FUNCTION_SEQ VARCHAR2(256), SYSTEM_TYPE VARCHAR2(64), CREATE_USER VARCHAR2(50), CREATE_DATE DATE, MODIFY_USER VARCHAR2(50), MODIFY_DATE DATE ) ; comment on table T_AUTH_FUNCTION is '权限功能表'; comment on column T_AUTH_FUNCTION.ID is '主键'; comment on column T_AUTH_FUNCTION.FUNCTION_CODE is '功能编码'; comment on column T_AUTH_FUNCTION.FUNCTION_NAME is '功能名称'; comment on column T_AUTH_FUNCTION.URI is '功能入口URI'; comment on column T_AUTH_FUNCTION.FUNCTION_LEVEL is '功能层级'; comment on column T_AUTH_FUNCTION.PARENT_CODE is '上级功能编码'; comment on column T_AUTH_FUNCTION.ACTIVE is '是否有效'; comment on column T_AUTH_FUNCTION.DISPLAY_ORDER is '显示顺序'; comment on column T_AUTH_FUNCTION.CHECKABLE is '是否权限检查'; comment on column T_AUTH_FUNCTION.FUNCTION_TYPE is '功能类型'; comment on column T_AUTH_FUNCTION.LEAF is '是否叶子节点'; comment on column T_AUTH_FUNCTION.ICON_CLS is '图标的CSS样式'; comment on column T_AUTH_FUNCTION.CLS is '节点的CSS样式'; comment on column T_AUTH_FUNCTION.FUNCTION_DESC is '功能描述'; comment on column T_AUTH_FUNCTION.FUNCTION_SEQ is '功能路径描述'; comment on column T_AUTH_FUNCTION.SYSTEM_TYPE is '所属系统类型(WEB/GUI)'; comment on column T_AUTH_FUNCTION.CREATE_USER is '创建人'; comment on column T_AUTH_FUNCTION.CREATE_DATE is '创建日期'; comment on column T_AUTH_FUNCTION.MODIFY_USER is '修改人'; comment on column T_AUTH_FUNCTION.MODIFY_DATE is '修改日期'; alter table T_AUTH_FUNCTION add primary key (ID); create table T_AUTH_FUNCTIONROLE ( ID VARCHAR2(50) not null, ROLE_CODE VARCHAR2(64) not null, FUNCTION_CODE VARCHAR2(64) not null ) ; comment on table T_AUTH_FUNCTIONROLE is '角色权限关联表'; comment on column T_AUTH_FUNCTIONROLE.ID is '主键'; comment on column T_AUTH_FUNCTIONROLE.ROLE_CODE is '角色编码'; comment on column T_AUTH_FUNCTIONROLE.FUNCTION_CODE is '功能编码'; alter table T_AUTH_FUNCTIONROLE add primary key (ID); create table T_AUTH_FUNCTION_CONFLICT ( ID VARCHAR2(50) not null, VIRTUAL_CODE VARCHAR2(50), FIRST_CODE VARCHAR2(50), SECOND_CODE VARCHAR2(50), ROLE_CODE VARCHAR2(50), CREATE_TIME DATE, MODIFY_TIME DATE, ACTIVE CHAR(1), CREATE_USER_CODE VARCHAR2(50), MODIFY_USER_CODE VARCHAR2(50) ) ; comment on column T_AUTH_FUNCTION_CONFLICT.ID is 'ID'; comment on column T_AUTH_FUNCTION_CONFLICT.VIRTUAL_CODE is '虚拟编码'; comment on column T_AUTH_FUNCTION_CONFLICT.FIRST_CODE is '权限编码一'; comment on column T_AUTH_FUNCTION_CONFLICT.SECOND_CODE is '权限编码二'; comment on column T_AUTH_FUNCTION_CONFLICT.ROLE_CODE is '角色编码'; comment on column T_AUTH_FUNCTION_CONFLICT.CREATE_TIME is '创建时间'; comment on column T_AUTH_FUNCTION_CONFLICT.MODIFY_TIME is '更新时间'; comment on column T_AUTH_FUNCTION_CONFLICT.ACTIVE is '是否启用'; comment on column T_AUTH_FUNCTION_CONFLICT.CREATE_USER_CODE is '创建人'; comment on column T_AUTH_FUNCTION_CONFLICT.MODIFY_USER_CODE is '更新人'; create table T_AUTH_ROLE ( ID VARCHAR2(50) not null, ROLE_CODE VARCHAR2(64), ROLE_NAME VARCHAR2(64), ROLE_DESC VARCHAR2(256), ACTIVE VARCHAR2(8), CREATE_USER VARCHAR2(50), CREATE_DATE DATE, MODIFY_USER VARCHAR2(50), MODIFY_DATE DATE ) ; comment on column T_AUTH_ROLE.ID is '主键'; comment on column T_AUTH_ROLE.ROLE_CODE is '角色编码'; comment on column T_AUTH_ROLE.ROLE_NAME is '角色名称'; comment on column T_AUTH_ROLE.ROLE_DESC is '角色描述'; comment on column T_AUTH_ROLE.ACTIVE is '是否有效'; comment on column T_AUTH_ROLE.CREATE_USER is '创建人'; comment on column T_AUTH_ROLE.CREATE_DATE is '创建时间'; comment on column T_AUTH_ROLE.MODIFY_USER is '修改人'; comment on column T_AUTH_ROLE.MODIFY_DATE is '修改时间'; alter table T_AUTH_ROLE add primary key (ID); create table T_AUTH_USER ( EMP_CODE VARCHAR2(64) not null, LOGIN_NAME VARCHAR2(64), PASSWORD VARCHAR2(64), LAST_LOGIN DATE, CREATE_USER VARCHAR2(64), CREATE_DATE DATE, MODIFY_USER VARCHAR2(64), MODIFY_DATE DATE, BEGIN_TIME DATE, END_TIME DATE, ACTIVE CHAR(1), ID VARCHAR2(50) not null ) ; comment on table T_AUTH_USER is '权限用户表'; comment on column T_AUTH_USER.EMP_CODE is '职员编号（FK）'; comment on column T_AUTH_USER.LOGIN_NAME is '登录用户名'; comment on column T_AUTH_USER.PASSWORD is '密码'; comment on column T_AUTH_USER.LAST_LOGIN is '最近登录时间'; comment on column T_AUTH_USER.CREATE_USER is '创建人'; comment on column T_AUTH_USER.CREATE_DATE is '创建时间'; comment on column T_AUTH_USER.MODIFY_USER is '最后修改人'; comment on column T_AUTH_USER.MODIFY_DATE is '最后修改时间'; comment on column T_AUTH_USER.BEGIN_TIME is '开始日期'; comment on column T_AUTH_USER.END_TIME is '结束日期'; comment on column T_AUTH_USER.ACTIVE is '用户状态'; comment on column T_AUTH_USER.ID is 'ID'; alter table T_AUTH_USER add constraint PK_T_AUTH_USER primary key (ID); alter table T_AUTH_USER add constraint UK_T_AUTH_USER_EMP_CODE unique (EMP_CODE); create table T_AUTH_USERDEPARTMENT ( ID VARCHAR2(50) not null, USER_CODE VARCHAR2(50), ORG_CODE VARCHAR2(50), CREATE_TIME DATE, MODIFY_TIME DATE, CREATE_USER_CODE VARCHAR2(50), MODIFY_USER_CODE VARCHAR2(50), INCLUDE_SUB_ORG VARCHAR2(1) ) ; alter table T_AUTH_USERDEPARTMENT add primary key (ID); create table T_AUTH_USERROLE ( ID VARCHAR2(50) not null, ROLE_CODE VARCHAR2(64) not null, USER_CODE VARCHAR2(64) not null ) ; alter table T_AUTH_USERROLE add constraint PK_T_AUTH_USERROLE_ID primary key (ID); create table T_AUTH_USER_MENU ( ID VARCHAR2(50) not null, USER_CODE VARCHAR2(50), FUNCTION_CODE VARCHAR2(50), DISPLAY_ORDER NUMBER(10), CREATE_TIME DATE, MODIFY_TIME DATE, ACTIVE CHAR(1) default 'Y', CREATE_USER_CODE VARCHAR2(50), MODIFY_USER_CODE VARCHAR2(50) ) ; alter table T_AUTH_USER_MENU add primary key (ID); create table T_AUTH_USER_ORG_AUTH ( EMP_CODE VARCHAR2(50), ORG_CODE VARCHAR2(50) ) ; create table T_BAS_COURIER_SCHEDULE ( ID VARCHAR2(50) not null, SMALL_REGIONS_CODE VARCHAR2(50), SMALL_REGIONS_NAME VARCHAR2(200), COURIER_CODE VARCHAR2(50), SCHEDULE_DATE TIMESTAMP(6), START_FLOOR NUMBER(10), END_FLOOR NUMBER(10), COURIER_NATURE VARCHAR2(50), PLAN_TYPE VARCHAR2(50), EXPRESS_PART_CODE VARCHAR2(50), EXPRESS_PART_NAME VARCHAR2(200), CREATE_TIME TIMESTAMP(6), MODIFY_TIME TIMESTAMP(6), ACTIVE CHAR(1), CREATE_USER_CODE VARCHAR2(50), MODIFY_USER_CODE VARCHAR2(50), VERSION_NO NUMBER(18), DATE_NUM NUMBER, YEAR_MONTH VARCHAR2(10) ) ; comment on table T_BAS_COURIER_SCHEDULE is '快递员排班表'; comment on column T_BAS_COURIER_SCHEDULE.SMALL_REGIONS_CODE is '收派小区编码'; comment on column T_BAS_COURIER_SCHEDULE.SMALL_REGIONS_NAME is '收派小区名称'; comment on column T_BAS_COURIER_SCHEDULE.COURIER_CODE is '所属快递员'; comment on column T_BAS_COURIER_SCHEDULE.SCHEDULE_DATE is '排班日期'; comment on column T_BAS_COURIER_SCHEDULE.START_FLOOR is '起始楼层'; comment on column T_BAS_COURIER_SCHEDULE.END_FLOOR is '结束楼层'; comment on column T_BAS_COURIER_SCHEDULE.COURIER_NATURE is '快递员属性'; comment on column T_BAS_COURIER_SCHEDULE.PLAN_TYPE is '工作类别'; comment on column T_BAS_COURIER_SCHEDULE.EXPRESS_PART_CODE is '所属快递点部编码'; comment on column T_BAS_COURIER_SCHEDULE.EXPRESS_PART_NAME is '所属快递点部名称'; comment on column T_BAS_COURIER_SCHEDULE.CREATE_TIME is '创建时间'; comment on column T_BAS_COURIER_SCHEDULE.MODIFY_TIME is '更新时间'; comment on column T_BAS_COURIER_SCHEDULE.ACTIVE is '是否启用'; comment on column T_BAS_COURIER_SCHEDULE.CREATE_USER_CODE is '创建人'; comment on column T_BAS_COURIER_SCHEDULE.MODIFY_USER_CODE is '更新人'; comment on column T_BAS_COURIER_SCHEDULE.VERSION_NO is '数据版本'; alter table T_BAS_COURIER_SCHEDULE add constraint PK_T_BAS_COURIER_SCHEDULE_ID primary key (ID); create index IDX_COURIER_REGIONS_CODE on T_BAS_COURIER_SCHEDULE (SMALL_REGIONS_CODE); create index IDX_COURIER_SCDATE_CODE on T_BAS_COURIER_SCHEDULE (COURIER_CODE, TO_CHAR(SCHEDULE_DATE,'yyyymmdd')); create index IDX_CR_SCHEDULE_COURIER_CODE on T_BAS_COURIER_SCHEDULE (COURIER_CODE); create table T_BAS_EXPRESS_SMALLZONE ( ID VARCHAR2(50) not null, REGION_CODE VARCHAR2(50), REGION_NAME VARCHAR2(200), MANAGEMENT VARCHAR2(50), CREATE_TIME TIMESTAMP(6), MODIFY_TIME TIMESTAMP(6), ACTIVE CHAR(1) default 1, CREATE_USER_CODE VARCHAR2(50), MODIFY_USER_CODE VARCHAR2(50), NOTES VARCHAR2(1000), VIRTUAL_CODE VARCHAR2(50), REGION_TYPE VARCHAR2(50), GISID VARCHAR2(50), BIGZONECODE VARCHAR2(50), PROVCODE VARCHAR2(100), CITYCODE VARCHAR2(100), COUNTYCODE VARCHAR2(100), GIS_AREA VARCHAR2(50), OPERATOR_CODE VARCHAR2(50), COURIERCODE VARCHAR2(100), COURIERNAME VARCHAR2(100) ) ; comment on table T_BAS_EXPRESS_SMALLZONE is '快递收派小区'; comment on column T_BAS_EXPRESS_SMALLZONE.ID is '主键'; comment on column T_BAS_EXPRESS_SMALLZONE.REGION_CODE is '小区编码'; comment on column T_BAS_EXPRESS_SMALLZONE.REGION_NAME is '小区名称'; comment on column T_BAS_EXPRESS_SMALLZONE.MANAGEMENT is '管理部门'; comment on column T_BAS_EXPRESS_SMALLZONE.CREATE_TIME is '创建时间'; comment on column T_BAS_EXPRESS_SMALLZONE.MODIFY_TIME is '更新时间'; comment on column T_BAS_EXPRESS_SMALLZONE.ACTIVE is '是否启用'; comment on column T_BAS_EXPRESS_SMALLZONE.CREATE_USER_CODE is '创建人'; comment on column T_BAS_EXPRESS_SMALLZONE.MODIFY_USER_CODE is '更新人'; comment on column T_BAS_EXPRESS_SMALLZONE.NOTES is '备注'; comment on column T_BAS_EXPRESS_SMALLZONE.VIRTUAL_CODE is '虚拟编码'; comment on column T_BAS_EXPRESS_SMALLZONE.REGION_TYPE is '小区类型：CBD区域、专业市场、商业区、住宅区、商住混合区、其他'; comment on column T_BAS_EXPRESS_SMALLZONE.GISID is 'GIS系统地图范围ID'; comment on column T_BAS_EXPRESS_SMALLZONE.BIGZONECODE is '所属大区虚拟编码'; comment on column T_BAS_EXPRESS_SMALLZONE.PROVCODE is '所在省'; comment on column T_BAS_EXPRESS_SMALLZONE.CITYCODE is '所在市'; comment on column T_BAS_EXPRESS_SMALLZONE.COUNTYCODE is '所在区县'; comment on column T_BAS_EXPRESS_SMALLZONE.GIS_AREA is '面积'; comment on column T_BAS_EXPRESS_SMALLZONE.OPERATOR_CODE is '操作人工号'; alter table T_BAS_EXPRESS_SMALLZONE add primary key (ID); create table T_DICTIONARY_DATA ( ID VARCHAR2(50) not null, DICT_TYPE VARCHAR2(50), VALUE_ORDER NUMBER(9), VALUE_NAME VARCHAR2(50), VALUE_CODE VARCHAR2(50), LANGUAGE VARCHAR2(10), ACTIVE CHAR(1), CREATE_TIME DATE, MODIFY_TIME DATE, CREATE_USER_CODE VARCHAR2(50), MODIFY_USER_CODE VARCHAR2(50), NOTE_INFO VARCHAR2(250), VERSION_NO NUMBER(18) default 0 ) ; comment on table T_DICTIONARY_DATA is '数据字典基础数据表'; comment on column T_DICTIONARY_DATA.ID is 'ID'; comment on column T_DICTIONARY_DATA.DICT_TYPE is '数据字典分类'; comment on column T_DICTIONARY_DATA.VALUE_ORDER is '序号'; comment on column T_DICTIONARY_DATA.VALUE_NAME is '值名称'; comment on column T_DICTIONARY_DATA.VALUE_CODE is '值代码'; comment on column T_DICTIONARY_DATA.LANGUAGE is '语言'; comment on column T_DICTIONARY_DATA.ACTIVE is '是否启用'; comment on column T_DICTIONARY_DATA.CREATE_TIME is '创建时间'; comment on column T_DICTIONARY_DATA.MODIFY_TIME is '更新时间'; comment on column T_DICTIONARY_DATA.CREATE_USER_CODE is '创建人'; comment on column T_DICTIONARY_DATA.MODIFY_USER_CODE is '更新人'; comment on column T_DICTIONARY_DATA.NOTE_INFO is '备注信息'; alter table T_DICTIONARY_DATA add primary key (ID); create table T_DICTIONARY_TYPE ( ID VARCHAR2(50) not null, TYPE_NAME VARCHAR2(50), ACTIVE CHAR(1), CREATE_TIME DATE, MODIFY_TIME DATE, CREATE_USER_CODE VARCHAR2(50), MODIFY_USER_CODE VARCHAR2(50), PARENT_CODE VARCHAR2(50), TYPE_ALIAS VARCHAR2(50), IS_LEAF CHAR(1) ) ; comment on table T_DICTIONARY_TYPE is '数据字典分类表'; comment on column T_DICTIONARY_TYPE.ID is 'ID'; comment on column T_DICTIONARY_TYPE.TYPE_NAME is '分类名称'; comment on column T_DICTIONARY_TYPE.ACTIVE is '是否启用'; comment on column T_DICTIONARY_TYPE.CREATE_TIME is '创建时间'; comment on column T_DICTIONARY_TYPE.MODIFY_TIME is '更新时间'; comment on column T_DICTIONARY_TYPE.CREATE_USER_CODE is '创建人'; comment on column T_DICTIONARY_TYPE.MODIFY_USER_CODE is '更新人'; comment on column T_DICTIONARY_TYPE.PARENT_CODE is '上级分类编码'; comment on column T_DICTIONARY_TYPE.TYPE_ALIAS is '分类别名'; comment on column T_DICTIONARY_TYPE.IS_LEAF is '是否为子分类'; alter table T_DICTIONARY_TYPE add primary key (ID); create table T_DSJOB_ODS_TO_EVS_LOG ( INSERT_TIME DATE, TABLE_NAME VARCHAR2(100), ENABLE_FLAG CHAR(1) ) ; create table T_EVS_BAS_AREAID ( AREAID VARCHAR2(10), NAMEEN VARCHAR2(50), NAMECN VARCHAR2(50), DISTRICTEN VARCHAR2(50), DISTRICTCN VARCHAR2(50), PROVEN VARCHAR2(50), PROVCN VARCHAR2(50), NATIONEN VARCHAR2(50), NATIONCN VARCHAR2(50) ) ; create table T_EVS_COURIER_ATTENDANCE ( DATE_TIME VARCHAR2(8), ATTENDANCE_DAYS NUMBER, IS_WORK_DAY VARCHAR2(1) ) ; comment on table T_EVS_COURIER_ATTENDANCE is '快递员标准出勤表'; comment on column T_EVS_COURIER_ATTENDANCE.DATE_TIME is '日期'; comment on column T_EVS_COURIER_ATTENDANCE.ATTENDANCE_DAYS is '应出勤天数'; comment on column T_EVS_COURIER_ATTENDANCE.IS_WORK_DAY is '是否工作天'; alter table T_EVS_COURIER_ATTENDANCE add check (is_work_day in ('Y','N')); create table T_EVS_COURIER_BIGZONE ( ID VARCHAR2(64) not null, CODE VARCHAR2(32), PRODUCT_DATE VARCHAR2(8), COURIER_NUMBER NUMBER ) ; comment on table T_EVS_COURIER_BIGZONE is '快递大区'; comment on column T_EVS_COURIER_BIGZONE.ID is 'id'; comment on column T_EVS_COURIER_BIGZONE.CODE is '组织编码'; comment on column T_EVS_COURIER_BIGZONE.PRODUCT_DATE is '生产日期'; comment on column T_EVS_COURIER_BIGZONE.COURIER_NUMBER is '大区快递员人数'; alter table T_EVS_COURIER_BIGZONE add primary key (ID); create table T_EVS_COURIER_CUMULATIVEDATA ( ID VARCHAR2(64) not null, PRODUCT_DATE VARCHAR2(8) not null, COURIER_CODE VARCHAR2(64) not null, COURIER_NAME VARCHAR2(64) not null, SEND_EFFICIENCY NUMBER, INCOME NUMBER, URGE_RECEIVE NUMBER, URGE_SEND NUMBER, MISTAKE NUMBER, COMPLAIN NUMBER, TRAFFIC_ACCIDENT NUMBER, CONTRABAND NUMBER, LOST_GOODS NUMBER, ABNORMAL_SIGN NUMBER, PRAISE NUMBER, ABSENCE_DAYS NUMBER, CREATE_DATE DATE, CREATE_USER VARCHAR2(64), MODIFY_USER VARCHAR2(64), MODIFY_DATE DATE, WORK_DAYS NUMBER ) ; comment on table T_EVS_COURIER_CUMULATIVEDATA is '快递员当月累计数据表'; comment on column T_EVS_COURIER_CUMULATIVEDATA.ID is 'id'; comment on column T_EVS_COURIER_CUMULATIVEDATA.PRODUCT_DATE is '日期'; comment on column T_EVS_COURIER_CUMULATIVEDATA.COURIER_CODE is '快递员工号'; comment on column T_EVS_COURIER_CUMULATIVEDATA.COURIER_NAME is '快递员姓名'; comment on column T_EVS_COURIER_CUMULATIVEDATA.SEND_EFFICIENCY is '日均派件票数'; comment on column T_EVS_COURIER_CUMULATIVEDATA.INCOME is '开单金额'; comment on column T_EVS_COURIER_CUMULATIVEDATA.URGE_RECEIVE is '催收个数'; comment on column T_EVS_COURIER_CUMULATIVEDATA.URGE_SEND is '催派个数'; comment on column T_EVS_COURIER_CUMULATIVEDATA.MISTAKE is '差错'; comment on column T_EVS_COURIER_CUMULATIVEDATA.COMPLAIN is '投诉个数'; comment on column T_EVS_COURIER_CUMULATIVEDATA.TRAFFIC_ACCIDENT is '交通事故'; comment on column T_EVS_COURIER_CUMULATIVEDATA.CONTRABAND is '违禁品'; comment on column T_EVS_COURIER_CUMULATIVEDATA.LOST_GOODS is '丢货'; comment on column T_EVS_COURIER_CUMULATIVEDATA.ABNORMAL_SIGN is '异常签收'; comment on column T_EVS_COURIER_CUMULATIVEDATA.PRAISE is '表扬'; comment on column T_EVS_COURIER_CUMULATIVEDATA.ABSENCE_DAYS is '缺勤天数'; comment on column T_EVS_COURIER_CUMULATIVEDATA.CREATE_DATE is '创建时间'; comment on column T_EVS_COURIER_CUMULATIVEDATA.CREATE_USER is '创建人'; comment on column T_EVS_COURIER_CUMULATIVEDATA.MODIFY_USER is '修改人'; comment on column T_EVS_COURIER_CUMULATIVEDATA.MODIFY_DATE is '修改时间'; comment on column T_EVS_COURIER_CUMULATIVEDATA.WORK_DAYS is '快递员工作日天数'; alter table T_EVS_COURIER_CUMULATIVEDATA add primary key (ID); create index ID_CUMULATIVEDATA_DATE_CODE on T_EVS_COURIER_CUMULATIVEDATA (PRODUCT_DATE, COURIER_CODE); create table T_EVS_COURIER_DATA ( ID VARCHAR2(64) not null, PRODUCT_DATE VARCHAR2(8) not null, COURIER_CODE VARCHAR2(64) not null, COURIER_NAME VARCHAR2(64), RECEIVE NUMBER, SEND NUMBER, INCOME NUMBER, COMPLAIN NUMBER, URGE_RECEIVE NUMBER, URGE_SEND NUMBER, MISTAKE NUMBER, TRAFFIC_ACCIDENT NUMBER, CONTRABAND NUMBER, LOST_GOODS NUMBER, ABNORMAL_SIGN NUMBER, PRAISE NUMBER, IS_ABSENCE CHAR(1), CREATE_DATE DATE, CREATE_USER VARCHAR2(64), MODIFY_USER VARCHAR2(64), MODIFY_DATE DATE, IS_WORK_DAY CHAR(1), CITY_CATE VARCHAR2(64) ) ; comment on table T_EVS_COURIER_DATA is '快递员数据表'; comment on column T_EVS_COURIER_DATA.ID is 'id'; comment on column T_EVS_COURIER_DATA.PRODUCT_DATE is '日期'; comment on column T_EVS_COURIER_DATA.COURIER_CODE is '快递员工号'; comment on column T_EVS_COURIER_DATA.COURIER_NAME is '快递员姓名'; comment on column T_EVS_COURIER_DATA.RECEIVE is '收件票数'; comment on column T_EVS_COURIER_DATA.SEND is '派送票数'; comment on column T_EVS_COURIER_DATA.INCOME is '收入'; comment on column T_EVS_COURIER_DATA.COMPLAIN is '投诉数'; comment on column T_EVS_COURIER_DATA.URGE_RECEIVE is '催收票数'; comment on column T_EVS_COURIER_DATA.URGE_SEND is '催派票数'; comment on column T_EVS_COURIER_DATA.MISTAKE is '差错'; comment on column T_EVS_COURIER_DATA.TRAFFIC_ACCIDENT is '交通事故'; comment on column T_EVS_COURIER_DATA.CONTRABAND is '违禁品'; comment on column T_EVS_COURIER_DATA.LOST_GOODS is '丢货'; comment on column T_EVS_COURIER_DATA.ABNORMAL_SIGN is '异常签收'; comment on column T_EVS_COURIER_DATA.PRAISE is '表扬'; comment on column T_EVS_COURIER_DATA.IS_ABSENCE is '是否缺勤,Y:是,N:否'; comment on column T_EVS_COURIER_DATA.CREATE_DATE is '创建时间'; comment on column T_EVS_COURIER_DATA.CREATE_USER is '创建人'; comment on column T_EVS_COURIER_DATA.MODIFY_USER is '修改人'; comment on column T_EVS_COURIER_DATA.MODIFY_DATE is '修改时间'; comment on column T_EVS_COURIER_DATA.IS_WORK_DAY is '是否快递员工作日,Y:是,N:否'; comment on column T_EVS_COURIER_DATA.CITY_CATE is '城市类别'; alter table T_EVS_COURIER_DATA add primary key (ID); create index IDX_COURIER_DATA_CODE on T_EVS_COURIER_DATA (COURIER_CODE); create index IDX_COURIER_DATA_DATE_CODE on T_EVS_COURIER_DATA (PRODUCT_DATE, COURIER_CODE); create table T_EVS_COURIER_DATA_UPLOAD ( ID VARCHAR2(64) not null, COURIER_CODE VARCHAR2(64) not null, PRODUCT_DATE VARCHAR2(64) not null, TRAFFIC_ACCIDENT NUMBER, LOST_GOODS NUMBER, PRAISE NUMBER, CREATE_USER VARCHAR2(64), CREATE_DATE DATE, MODIFY_USER VARCHAR2(64), MODIFY_DATE DATE ) ; comment on table T_EVS_COURIER_DATA_UPLOAD is '日数据上传表'; comment on column T_EVS_COURIER_DATA_UPLOAD.ID is 'id'; comment on column T_EVS_COURIER_DATA_UPLOAD.COURIER_CODE is '快递员工号'; comment on column T_EVS_COURIER_DATA_UPLOAD.PRODUCT_DATE is '日期'; comment on column T_EVS_COURIER_DATA_UPLOAD.TRAFFIC_ACCIDENT is '交通事故'; comment on column T_EVS_COURIER_DATA_UPLOAD.LOST_GOODS is '丢货次数'; comment on column T_EVS_COURIER_DATA_UPLOAD.PRAISE is '表扬次数'; comment on column T_EVS_COURIER_DATA_UPLOAD.CREATE_USER is '创建人'; comment on column T_EVS_COURIER_DATA_UPLOAD.CREATE_DATE is '创建时间'; comment on column T_EVS_COURIER_DATA_UPLOAD.MODIFY_USER is '修改人'; comment on column T_EVS_COURIER_DATA_UPLOAD.MODIFY_DATE is '修改时间'; alter table T_EVS_COURIER_DATA_UPLOAD add primary key (ID); create table T_EVS_COURIER_DATA_WARNING ( ID VARCHAR2(50) not null, COURIER_CODE VARCHAR2(64), PRODUCT_DATE VARCHAR2(8), CONTENT VARCHAR2(2000), CREATE_DATE DATE ) ; comment on table T_EVS_COURIER_DATA_WARNING is '预警提醒数据表'; comment on column T_EVS_COURIER_DATA_WARNING.ID is 'id'; comment on column T_EVS_COURIER_DATA_WARNING.COURIER_CODE is '快递员工号'; comment on column T_EVS_COURIER_DATA_WARNING.PRODUCT_DATE is '时间'; comment on column T_EVS_COURIER_DATA_WARNING.CONTENT is '内容'; comment on column T_EVS_COURIER_DATA_WARNING.CREATE_DATE is '创建时间'; alter table T_EVS_COURIER_DATA_WARNING add primary key (ID); create table T_EVS_COURIER_INFO ( ID VARCHAR2(64) not null, CREATE_DATE DATE, COURIER_CODE VARCHAR2(32), PRODUCT_DATE VARCHAR2(8), MANAGE_DEPT VARCHAR2(64), MANAGE_DEPT_NAME VARCHAR2(64), CAREER_DEPT VARCHAR2(64), CAREER_DEPT_NAME VARCHAR2(64), BIG_AREA VARCHAR2(64), BIG_AREA_NAME VARCHAR2(64), SMALL_AREA VARCHAR2(64), SMALL_AREA_NAME VARCHAR2(64), BUSINESS_EXPRESS VARCHAR2(64), BUSINESS_EXPRESS_NAME VARCHAR2(64) ) ; comment on table T_EVS_COURIER_INFO is '快递员所属 组织 及其父级组织'; comment on column T_EVS_COURIER_INFO.ID is 'id'; comment on column T_EVS_COURIER_INFO.CREATE_DATE is '创建时间'; comment on column T_EVS_COURIER_INFO.COURIER_CODE is '快递员工号'; comment on column T_EVS_COURIER_INFO.PRODUCT_DATE is '生产日期'; comment on column T_EVS_COURIER_INFO.MANAGE_DEPT is '经营本部'; comment on column T_EVS_COURIER_INFO.MANAGE_DEPT_NAME is '经营本部名称'; comment on column T_EVS_COURIER_INFO.CAREER_DEPT is '事业部'; comment on column T_EVS_COURIER_INFO.CAREER_DEPT_NAME is '事业部名称'; comment on column T_EVS_COURIER_INFO.BIG_AREA is '大区'; comment on column T_EVS_COURIER_INFO.BIG_AREA_NAME is '大区名称'; comment on column T_EVS_COURIER_INFO.SMALL_AREA is '小区'; comment on column T_EVS_COURIER_INFO.SMALL_AREA_NAME is '小区名称'; comment on column T_EVS_COURIER_INFO.BUSINESS_EXPRESS is '营业部 或者 点部'; comment on column T_EVS_COURIER_INFO.BUSINESS_EXPRESS_NAME is '营业部 或者 点部 名称'; alter table T_EVS_COURIER_INFO add primary key (ID); create index IDX_COURIER_INFO on T_EVS_COURIER_INFO (COURIER_CODE); create index IDX_COURIER_INFO_DATE_CODE on T_EVS_COURIER_INFO (PRODUCT_DATE, BUSINESS_EXPRESS); create table T_EVS_COURIER_SCORE ( ID VARCHAR2(64) not null, DATA_ID VARCHAR2(64) not null, PRODUCT_DATE VARCHAR2(8) not null, COURIER_CODE VARCHAR2(64) not null, COURIER_NAME VARCHAR2(64) not null, EFFICIENCY_SCORE NUMBER, INCOME_SCORE NUMBER, URGE_RECEIVE_SCORE NUMBER, URGE_SEND_SCORE NUMBER, MISTAKE_SCORE NUMBER, COMPLAIN_SCORE NUMBER, ACCIDENT_SCORE NUMBER, CONTRABAND_SCORE NUMBER, LOST_SCORE NUMBER, ABNORMAL_SCORE NUMBER, PRAISE_SCORE NUMBER, ABSENCE_SCORE NUMBER, TOTAL_SCORE NUMBER, CREATE_DATE DATE, CREATE_USER VARCHAR2(64), MODIFY_USER VARCHAR2(64), MODIFY_DATE DATE, BIG_REGION VARCHAR2(64), RANK_BIG_REGION NUMBER ) ; comment on table T_EVS_COURIER_SCORE is '快递员积分表'; comment on column T_EVS_COURIER_SCORE.ID is 'id'; comment on column T_EVS_COURIER_SCORE.DATA_ID is '统计数据id'; comment on column T_EVS_COURIER_SCORE.PRODUCT_DATE is '日期'; comment on column T_EVS_COURIER_SCORE.COURIER_CODE is '快递员工号'; comment on column T_EVS_COURIER_SCORE.COURIER_NAME is '快递员姓名'; comment on column T_EVS_COURIER_SCORE.EFFICIENCY_SCORE is '日均派件票数积分'; comment on column T_EVS_COURIER_SCORE.INCOME_SCORE is '收入积分'; comment on column T_EVS_COURIER_SCORE.URGE_RECEIVE_SCORE is '催收个数积分'; comment on column T_EVS_COURIER_SCORE.URGE_SEND_SCORE is '催派个数积分'; comment on column T_EVS_COURIER_SCORE.MISTAKE_SCORE is '差错积分'; comment on column T_EVS_COURIER_SCORE.COMPLAIN_SCORE is '投诉积分'; comment on column T_EVS_COURIER_SCORE.ACCIDENT_SCORE is '交通事故积分'; comment on column T_EVS_COURIER_SCORE.CONTRABAND_SCORE is '违禁品积分'; comment on column T_EVS_COURIER_SCORE.LOST_SCORE is '丢货积分'; comment on column T_EVS_COURIER_SCORE.ABNORMAL_SCORE is '异常签收积分'; comment on column T_EVS_COURIER_SCORE.PRAISE_SCORE is '表扬积分'; comment on column T_EVS_COURIER_SCORE.ABSENCE_SCORE is '缺勤天数积分'; comment on column T_EVS_COURIER_SCORE.TOTAL_SCORE is '总积分'; comment on column T_EVS_COURIER_SCORE.CREATE_DATE is '创建时间'; comment on column T_EVS_COURIER_SCORE.CREATE_USER is '创建人'; comment on column T_EVS_COURIER_SCORE.MODIFY_USER is '修改人'; comment on column T_EVS_COURIER_SCORE.MODIFY_DATE is '修改时间'; comment on column T_EVS_COURIER_SCORE.BIG_REGION is '所属大区'; comment on column T_EVS_COURIER_SCORE.RANK_BIG_REGION is '快递员在大区的排名'; alter table T_EVS_COURIER_SCORE add primary key (ID); create index IDX_COURIER_CODE on T_EVS_COURIER_SCORE (COURIER_CODE); create index IDX_COURIER_SCORE_CODE_DATE on T_EVS_COURIER_SCORE (COURIER_CODE, PRODUCT_DATE); create table T_EVS_ERROR_PICKTICKET ( EMP_CODE VARCHAR2(50), EMP_NAME VARCHAR2(200), DEPT_CODE VARCHAR2(50), DEPT_NAME VARCHAR2(200), CALENDAR_DATE DATE, COLLECTION_NUM NUMBER(28,4), RUSHSEND_NUM NUMBER(28,4), ERROR_NUM NUMBER(28,4), CONTRABAND_NUM NUMBER(28,4), COMPLAINTS_NUM NUMBER(28,4), THROWGOODS_NUM NUMBER(28,4), ABNORMAL_NUM NUMBER(28,4) ) ; comment on table T_EVS_ERROR_PICKTICKET is '快递可视化-差错相关催收催派'; comment on column T_EVS_ERROR_PICKTICKET.EMP_CODE is '快递员编码'; comment on column T_EVS_ERROR_PICKTICKET.EMP_NAME is '快递员名称'; comment on column T_EVS_ERROR_PICKTICKET.DEPT_CODE is '所属部门编码'; comment on column T_EVS_ERROR_PICKTICKET.DEPT_NAME is '所属部门名称'; comment on column T_EVS_ERROR_PICKTICKET.CALENDAR_DATE is '日期'; comment on column T_EVS_ERROR_PICKTICKET.COLLECTION_NUM is '客户催促快递员上门收件票数'; comment on column T_EVS_ERROR_PICKTICKET.RUSHSEND_NUM is '客户催促快递员送货票数'; comment on column T_EVS_ERROR_PICKTICKET.ERROR_NUM is '快递员差错个数'; comment on column T_EVS_ERROR_PICKTICKET.CONTRABAND_NUM is '快递员违禁品、拒收品差错个数'; comment on column T_EVS_ERROR_PICKTICKET.COMPLAINTS_NUM is '快递员投诉票数'; comment on column T_EVS_ERROR_PICKTICKET.THROWGOODS_NUM is '快递员责任累计丢货票数'; comment on column T_EVS_ERROR_PICKTICKET.ABNORMAL_NUM is '快递异常签收票数'; create table T_EVS_EXP_PERSONCITY ( DEPT_CODE VARCHAR2(50), DEPT_NAME VARCHAR2(200), EMP_CODE VARCHAR2(40), EMP_NAME VARCHAR2(200), JOBNAME VARCHAR2(100), PROVINCE_NAME VARCHAR2(200), CITY_NAME VARCHAR2(200), CITYTYPE_CODE VARCHAR2(100), CITYTYPE_NAME VARCHAR2(200) ) ; comment on table T_EVS_EXP_PERSONCITY is '快递员部门所属城市'; comment on column T_EVS_EXP_PERSONCITY.DEPT_CODE is '部门编码'; comment on column T_EVS_EXP_PERSONCITY.DEPT_NAME is '部门名称'; comment on column T_EVS_EXP_PERSONCITY.EMP_CODE is '快递员编码'; comment on column T_EVS_EXP_PERSONCITY.EMP_NAME is '快递员姓名'; comment on column T_EVS_EXP_PERSONCITY.JOBNAME is '职位'; comment on column T_EVS_EXP_PERSONCITY.PROVINCE_NAME is '部门所在省'; comment on column T_EVS_EXP_PERSONCITY.CITY_NAME is '部门所在市'; comment on column T_EVS_EXP_PERSONCITY.CITYTYPE_CODE is '部门所属城市类别编码'; comment on column T_EVS_EXP_PERSONCITY.CITYTYPE_NAME is '部门所属城市类别'; create index IDX_EXP_PERSONCITY_CODE on T_EVS_EXP_PERSONCITY (EMP_CODE); create table T_EVS_FEEDBACK ( ID VARCHAR2(64) not null, QUESTION_CODE VARCHAR2(64) not null, QUESTION_TYPE VARCHAR2(50), QUESTION_CONTENT VARCHAR2(1000), QUESTION_STATUS VARCHAR2(50), IS_RESENT CHAR(1), MODIFY_DATE DATE, MODIFY_USER VARCHAR2(10), CREATE_DATE DATE, CREATE_USER VARCHAR2(10), ORIGINAL_TYPE VARCHAR2(32) ) ; comment on table T_EVS_FEEDBACK is '问题反馈表'; comment on column T_EVS_FEEDBACK.ID is '主键'; comment on column T_EVS_FEEDBACK.QUESTION_CODE is '问题编码'; comment on column T_EVS_FEEDBACK.QUESTION_TYPE is '问题类型'; comment on column T_EVS_FEEDBACK.QUESTION_CONTENT is '问题内容'; comment on column T_EVS_FEEDBACK.QUESTION_STATUS is '问题状态'; comment on column T_EVS_FEEDBACK.IS_RESENT is '是否转发'; comment on column T_EVS_FEEDBACK.MODIFY_DATE is '修改时间'; comment on column T_EVS_FEEDBACK.MODIFY_USER is '修改人'; comment on column T_EVS_FEEDBACK.CREATE_DATE is '创建时间'; comment on column T_EVS_FEEDBACK.CREATE_USER is '创建人'; comment on column T_EVS_FEEDBACK.ORIGINAL_TYPE is '问题原始类型'; alter table T_EVS_FEEDBACK add primary key (ID); alter table T_EVS_FEEDBACK add constraint UNQ_T_EVS_FEEDBACK unique (QUESTION_CODE); create table T_EVS_FEEDBACK_FILE ( ID VARCHAR2(64) not null, QUESTION_CODE VARCHAR2(64) not null, FILE_NAME VARCHAR2(200) not null, FILE_ADDRESS VARCHAR2(500) not null ) ; comment on table T_EVS_FEEDBACK_FILE is '问题反馈文件表'; comment on column T_EVS_FEEDBACK_FILE.ID is '主键'; comment on column T_EVS_FEEDBACK_FILE.QUESTION_CODE is '问题编码'; comment on column T_EVS_FEEDBACK_FILE.FILE_NAME is '文件名称'; comment on column T_EVS_FEEDBACK_FILE.FILE_ADDRESS is '文件存放地址'; alter table T_EVS_FEEDBACK_FILE add constraint PK_ID primary key (ID); create table T_EVS_FEEDBACK_OPERATION ( ID VARCHAR2(64) not null, QUESTION_CODE VARCHAR2(64) not null, OPERATION_TYPE VARCHAR2(50), OPERATION_CONTENT VARCHAR2(1500), CREATE_DATE DATE, CREATE_USER VARCHAR2(10), QUESTION_TYPE VARCHAR2(50) ) ; comment on table T_EVS_FEEDBACK_OPERATION is '问题反馈操作记录表'; comment on column T_EVS_FEEDBACK_OPERATION.ID is '主键'; comment on column T_EVS_FEEDBACK_OPERATION.QUESTION_CODE is '问题编码'; comment on column T_EVS_FEEDBACK_OPERATION.OPERATION_TYPE is '操作类型'; comment on column T_EVS_FEEDBACK_OPERATION.OPERATION_CONTENT is '操作内容'; comment on column T_EVS_FEEDBACK_OPERATION.CREATE_DATE is '创建时间'; comment on column T_EVS_FEEDBACK_OPERATION.CREATE_USER is '创建人'; comment on column T_EVS_FEEDBACK_OPERATION.QUESTION_TYPE is '问题类型'; alter table T_EVS_FEEDBACK_OPERATION add primary key (ID); create table T_EVS_OPERATION ( ID VARCHAR2(64) not null, EMPCODE VARCHAR2(10), OPERATION_TIME DATE, OPERATION_TYPE VARCHAR2(200) ) ; comment on table T_EVS_OPERATION is '操作记录表'; comment on column T_EVS_OPERATION.ID is '主键'; comment on column T_EVS_OPERATION.EMPCODE is '操作人工号'; comment on column T_EVS_OPERATION.OPERATION_TIME is '操作时间'; comment on column T_EVS_OPERATION.OPERATION_TYPE is '操作类型'; alter table T_EVS_OPERATION add primary key (ID); create table T_EVS_ORGDATA_DAY ( ID VARCHAR2(64) not null, ORG_TYPE CHAR(1) not null, ORG_CODE VARCHAR2(64) not null, ORG_NAME VARCHAR2(256), PARANT_CODE VARCHAR2(64) not null, PRODUCT_DATE VARCHAR2(8) not null, INCOME NUMBER, RECEIVE NUMBER, SEND NUMBER, COMPLAIN NUMBER, URGE_RECEIVE NUMBER, URGE_SEND NUMBER, AVG_INCOME NUMBER, AVG_RECEIVE NUMBER, AVG_SEND NUMBER, AVG_URGE_RECEIVE NUMBER, AVG_URGE_SEND NUMBER, AVG_COMPLAIN NUMBER, AVG_EFFICIENCY NUMBER, COURIER_NUM NUMBER, CREATE_DATE DATE, CREATE_USER VARCHAR2(64), MODIFY_USER VARCHAR2(64), MODIFY_DATE DATE ) ; comment on table T_EVS_ORGDATA_DAY is '组织日统计结果表'; comment on column T_EVS_ORGDATA_DAY.ID is '主键'; comment on column T_EVS_ORGDATA_DAY.ORG_TYPE is '组织类型（1:小区,3:点部)'; comment on column T_EVS_ORGDATA_DAY.ORG_CODE is '组织编码'; comment on column T_EVS_ORGDATA_DAY.ORG_NAME is '组织名称'; comment on column T_EVS_ORGDATA_DAY.PARANT_CODE is '上级组织编码'; comment on column T_EVS_ORGDATA_DAY.PRODUCT_DATE is '生产日期'; comment on column T_EVS_ORGDATA_DAY.INCOME is '开单收入'; comment on column T_EVS_ORGDATA_DAY.RECEIVE is '总收件票数'; comment on column T_EVS_ORGDATA_DAY.SEND is '总派件票数'; comment on column T_EVS_ORGDATA_DAY.COMPLAIN is '总投诉'; comment on column T_EVS_ORGDATA_DAY.URGE_RECEIVE is '总催收票数'; comment on column T_EVS_ORGDATA_DAY.URGE_SEND is '总催派票数'; comment on column T_EVS_ORGDATA_DAY.AVG_INCOME is '人均收入'; comment on column T_EVS_ORGDATA_DAY.AVG_RECEIVE is '人均收件'; comment on column T_EVS_ORGDATA_DAY.AVG_SEND is '人均派件'; comment on column T_EVS_ORGDATA_DAY.AVG_URGE_RECEIVE is '人均催收（保留字段）'; comment on column T_EVS_ORGDATA_DAY.AVG_URGE_SEND is '人均催派（保留字段）'; comment on column T_EVS_ORGDATA_DAY.AVG_COMPLAIN is '人均投诉'; comment on column T_EVS_ORGDATA_DAY.AVG_EFFICIENCY is '人均效率'; comment on column T_EVS_ORGDATA_DAY.COURIER_NUM is '当日快递员人数(小区(排班表)、点部(oa))'; comment on column T_EVS_ORGDATA_DAY.CREATE_DATE is '创建时间'; comment on column T_EVS_ORGDATA_DAY.CREATE_USER is '创建人'; comment on column T_EVS_ORGDATA_DAY.MODIFY_USER is '修改人'; comment on column T_EVS_ORGDATA_DAY.MODIFY_DATE is '修改时间'; alter table T_EVS_ORGDATA_DAY add primary key (ID); create index IDX_ORGDATA_DAY_CODE on T_EVS_ORGDATA_DAY (ORG_CODE); create index IDX_PRODUCT_DATE on T_EVS_ORGDATA_DAY (PRODUCT_DATE); create table T_EVS_ORGDATA_MONTH ( ID VARCHAR2(64) not null, ORG_TYPE CHAR(1) not null, ORG_CODE VARCHAR2(64) not null, ORG_NAME VARCHAR2(256), PARANT_CODE VARCHAR2(64) not null, PRODUCT_DATE VARCHAR2(8) not null, INCOME NUMBER, RECEIVE NUMBER, SEND NUMBER, COMPLAIN NUMBER, URGE_RECEIVE NUMBER, URGE_SEND NUMBER, AVG_INCOME NUMBER, AVG_RECEIVE NUMBER, AVG_SEND NUMBER, AVG_URGE_RECEIVE NUMBER, AVG_URGE_SEND NUMBER, AVG_COMPLAIN NUMBER, AVG_EFFICIENCY NUMBER, COURIER_NUM NUMBER, CREATE_DATE DATE, CREATE_USER VARCHAR2(64), MODIFY_USER VARCHAR2(64), MODIFY_DATE DATE, AVG_SCORE NUMBER, CNT_COURIER_NUM NUMBER ) ; comment on table T_EVS_ORGDATA_MONTH is '组织月累计统计结果表'; comment on column T_EVS_ORGDATA_MONTH.ID is '主键'; comment on column T_EVS_ORGDATA_MONTH.ORG_TYPE is '组织类型（1:小区,3:点部)'; comment on column T_EVS_ORGDATA_MONTH.ORG_CODE is '组织编码'; comment on column T_EVS_ORGDATA_MONTH.ORG_NAME is '组织名称'; comment on column T_EVS_ORGDATA_MONTH.PARANT_CODE is '上级组织编码'; comment on column T_EVS_ORGDATA_MONTH.PRODUCT_DATE is '生产日期'; comment on column T_EVS_ORGDATA_MONTH.INCOME is '开单收入'; comment on column T_EVS_ORGDATA_MONTH.RECEIVE is '总收件票数'; comment on column T_EVS_ORGDATA_MONTH.SEND is '总派件票数'; comment on column T_EVS_ORGDATA_MONTH.COMPLAIN is '总投诉'; comment on column T_EVS_ORGDATA_MONTH.URGE_RECEIVE is '总催收票数'; comment on column T_EVS_ORGDATA_MONTH.URGE_SEND is '总催派票数'; comment on column T_EVS_ORGDATA_MONTH.AVG_INCOME is '人均收入'; comment on column T_EVS_ORGDATA_MONTH.AVG_RECEIVE is '人均收件'; comment on column T_EVS_ORGDATA_MONTH.AVG_SEND is '人均派件'; comment on column T_EVS_ORGDATA_MONTH.AVG_URGE_RECEIVE is '人均催收（保留字段）'; comment on column T_EVS_ORGDATA_MONTH.AVG_URGE_SEND is '人均催派（保留字段）'; comment on column T_EVS_ORGDATA_MONTH.AVG_COMPLAIN is '人均投诉'; comment on column T_EVS_ORGDATA_MONTH.AVG_EFFICIENCY is '人均效率'; comment on column T_EVS_ORGDATA_MONTH.COURIER_NUM is '当日快递员排班人数小区(排班表)、点部(oa)'; comment on column T_EVS_ORGDATA_MONTH.CREATE_DATE is '创建时间'; comment on column T_EVS_ORGDATA_MONTH.CREATE_USER is '创建人'; comment on column T_EVS_ORGDATA_MONTH.MODIFY_USER is '修改人'; comment on column T_EVS_ORGDATA_MONTH.MODIFY_DATE is '修改时间'; comment on column T_EVS_ORGDATA_MONTH.AVG_SCORE is '小区均分均值'; comment on column T_EVS_ORGDATA_MONTH.CNT_COURIER_NUM is '当月累计快递员排班人数小区(排班表)'; alter table T_EVS_ORGDATA_MONTH add primary key (ID); create index IDX_ORGDATA_MONTH_CODE on T_EVS_ORGDATA_MONTH (ORG_CODE); create table T_EVS_WAYBILLTICKET ( EMP_CODE VARCHAR2(50), EMP_NAME VARCHAR2(200), DEPT_CODE VARCHAR2(50), DEPT_NAME VARCHAR2(200), CALENDAR_DATE DATE, SENDTICKET_NUM NUMBER(28,4), RECEIPT_NUM NUMBER(28,4), TICKET_AMOUNT NUMBER(28,4) ) ; comment on table T_EVS_WAYBILLTICKET is '快递可视化--票相关'; comment on column T_EVS_WAYBILLTICKET.EMP_CODE is '快递员编码'; comment on column T_EVS_WAYBILLTICKET.EMP_NAME is '快递员名称'; comment on column T_EVS_WAYBILLTICKET.DEPT_CODE is '所属部门编码'; comment on column T_EVS_WAYBILLTICKET.DEPT_NAME is '所属部门名称'; comment on column T_EVS_WAYBILLTICKET.CALENDAR_DATE is '日期'; comment on column T_EVS_WAYBILLTICKET.SENDTICKET_NUM is '派件票数'; comment on column T_EVS_WAYBILLTICKET.RECEIPT_NUM is '收件票数'; comment on column T_EVS_WAYBILLTICKET.TICKET_AMOUNT is '开单收入'; create table T_LOGIN_CONFIG ( ID VARCHAR2(50) not null, BG_LOCATION VARCHAR2(200), LOGO_LOCATION VARCHAR2(200), LOGO_ALIGN VARCHAR2(200), LOGIN_BORDER_COLOR VARCHAR2(20), LOGIN_BORDER_SIZE VARCHAR2(20), LOGIN_BG_COLOR VARCHAR2(20), LOGIN_ALIGN VARCHAR2(50), IS_DISPLAY_DATE VARCHAR2(10), IS_HAS_CHECKCODE VARCHAR2(10), CSS_SOURCE VARCHAR2(2500), ACTIVE CHAR(1), CREATE_TIME TIMESTAMP(6), LOGIN_FONT_COLOR VARCHAR2(20), BUTTON_BORDER_COLOR VARCHAR2(20), BUTTON_FONT_COLOR VARCHAR2(20), BUTTON_BG_COLOR VARCHAR2(20), BUTTON_HOVER_BG_COLOR VARCHAR2(20), BUTTON_HOVER_FONT_COLOR VARCHAR2(20) ) ; comment on column T_LOGIN_CONFIG.BG_LOCATION is '存放背景图片位置'; comment on column T_LOGIN_CONFIG.LOGO_LOCATION is '存放LOGON图片位置'; comment on column T_LOGIN_CONFIG.LOGO_ALIGN is 'LOGO相对页面位置（居左、居右、居中）'; comment on column T_LOGIN_CONFIG.LOGIN_BORDER_COLOR is '登录边框的颜色'; comment on column T_LOGIN_CONFIG.LOGIN_BORDER_SIZE is '登录边框大小'; comment on column T_LOGIN_CONFIG.LOGIN_BG_COLOR is '登录框内背景颜色'; comment on column T_LOGIN_CONFIG.LOGIN_ALIGN is '登录框相对页面位置（居左、居右、居中）'; comment on column T_LOGIN_CONFIG.IS_DISPLAY_DATE is '是否显示日期'; comment on column T_LOGIN_CONFIG.IS_HAS_CHECKCODE is '是否需要验证码校验'; comment on column T_LOGIN_CONFIG.CSS_SOURCE is 'CSS样式源码'; comment on column T_LOGIN_CONFIG.ACTIVE is 'Y有限；N无效'; comment on column T_LOGIN_CONFIG.CREATE_TIME is '创建时间'; comment on column T_LOGIN_CONFIG.LOGIN_FONT_COLOR is '登录框内字体颜色'; comment on column T_LOGIN_CONFIG.BUTTON_BORDER_COLOR is '按钮边框颜色'; comment on column T_LOGIN_CONFIG.BUTTON_FONT_COLOR is '按钮字体颜色'; comment on column T_LOGIN_CONFIG.BUTTON_BG_COLOR is '按钮背景颜色'; comment on column T_LOGIN_CONFIG.BUTTON_HOVER_BG_COLOR is '鼠标移动上按钮的背景颜色'; comment on column T_LOGIN_CONFIG.BUTTON_HOVER_FONT_COLOR is '鼠标移动上字体的颜色'; alter table T_LOGIN_CONFIG add primary key (ID); create table T_LOGIN_MIANCONFIG ( ID VARCHAR2(50) not null, TITLE VARCHAR2(200), LOGO_LOCATION VARCHAR2(200), CSS_SOURCE VARCHAR2(2500), ACTIVE CHAR(1), CREATE_TIME TIMESTAMP(6) ) ; comment on table T_LOGIN_MIANCONFIG is '主框架配置表'; comment on column T_LOGIN_MIANCONFIG.ID is '主键'; comment on column T_LOGIN_MIANCONFIG.TITLE is '名称'; comment on column T_LOGIN_MIANCONFIG.LOGO_LOCATION is '存放LOGON图片路径'; comment on column T_LOGIN_MIANCONFIG.CSS_SOURCE is 'CSS样式源码'; comment on column T_LOGIN_MIANCONFIG.ACTIVE is 'Y有限；N无效'; comment on column T_LOGIN_MIANCONFIG.CREATE_TIME is '创建时间'; alter table T_LOGIN_MIANCONFIG add primary key (ID); create table T_MSG_ALERT_SETTING ( ID VARCHAR2(50) not null, USER_CODE VARCHAR2(50), INTERVAL_TIME NUMBER(9), AUTO_ALERT_FLAG CHAR(1), CREATE_TIME TIMESTAMP(6) ) ; alter table T_MSG_ALERT_SETTING add primary key (ID); create table T_MSG_INSTATION ( ID VARCHAR2(100) not null, SEND_USER_CODE VARCHAR2(50), RECEIVE_USER_CODE VARCHAR2(50), CONTEXT VARCHAR2(1000), MSG_TYPE VARCHAR2(20), ACTIVE CHAR(1), CREATE_TYPE VARCHAR2(20), IS_READED VARCHAR2(10), CREATE_TIME TIMESTAMP(6), EXPIRE_TIME TIMESTAMP(6), SEND_USER_NAME VARCHAR2(200), SEND_ORG_CODE VARCHAR2(200), SEND_ORG_NAME VARCHAR2(200), RECEIVE_USER_NAME VARCHAR2(200), RECEIVE_ORG_CODE VARCHAR2(200), RECEIVE_ORG_NAME VARCHAR2(200), RECEIVE_SUB_ORG_CODE VARCHAR2(200), RECEIVE_SUB_ORG_NAME VARCHAR2(200), RECEIVE_ROLE_CODE VARCHAR2(200), RECEIVE_TYPE VARCHAR2(20), STATUS VARCHAR2(1), MODIFY_TIME TIMESTAMP(6), MODIFY_USER_CODE VARCHAR2(50), MODIFY_USER_NAME VARCHAR2(50), SERIAL_NUMBER VARCHAR2(50) ) ; alter table T_MSG_INSTATION add primary key (ID); create table T_MSG_TODO ( ID VARCHAR2(50) not null, TITLE VARCHAR2(50), RECEIVE_ORG_CODE VARCHAR2(100), RECEIVE_SUB_ORG_CODE VARCHAR2(50), RECEIVE_SUB_ORG_NAME VARCHAR2(100), RECEIVE_ROLE_CODE VARCHAR2(50), RECEIVE_TYPE VARCHAR2(20), BUSINESS_TYPE VARCHAR2(20), SERIAL_NUMBER VARCHAR2(50), BUSINESS_NO VARCHAR2(50), DEAL_URL VARCHAR2(300), STATUS VARCHAR2(20), CREATE_TIME TIMESTAMP(6), CREATE_USER_CODE VARCHAR2(100), CREATE_USER_NAME VARCHAR2(100) ) ; alter table T_MSG_TODO add primary key (ID); create table T_ODS_DATA_1 ( COURIER_CODE VARCHAR2(10), ORG_CODE VARCHAR2(50), QUOTA_CODE NVARCHAR2(50), QUOTA_NAME VARCHAR2(50), QUOTA_DATE INTEGER, QUOTA_VALUE NUMBER ) ; comment on table T_ODS_DATA_1 is '指标数据表1'; comment on column T_ODS_DATA_1.COURIER_CODE is '快递员工号'; comment on column T_ODS_DATA_1.ORG_CODE is '部门编码'; comment on column T_ODS_DATA_1.QUOTA_CODE is '指标编码'; comment on column T_ODS_DATA_1.QUOTA_NAME is '指标名称'; comment on column T_ODS_DATA_1.QUOTA_DATE is '日期'; comment on column T_ODS_DATA_1.QUOTA_VALUE is '指标值'; create table T_ODS_DATA_2 ( COURIER_CODE VARCHAR2(10), ORG_CODE VARCHAR2(50), QUOTA_CODE NVARCHAR2(50), QUOTA_NAME VARCHAR2(50), QUOTA_DATE INTEGER, QUOTA_VALUE NUMBER ) ; comment on table T_ODS_DATA_2 is '指标数据表2'; comment on column T_ODS_DATA_2.COURIER_CODE is '快递员工号'; comment on column T_ODS_DATA_2.ORG_CODE is '部门编码'; comment on column T_ODS_DATA_2.QUOTA_CODE is '指标编码'; comment on column T_ODS_DATA_2.QUOTA_NAME is '指标名称'; comment on column T_ODS_DATA_2.QUOTA_DATE is '日期'; comment on column T_ODS_DATA_2.QUOTA_VALUE is '指标值'; create table T_ODS_DATA_3 ( COURIER_CODE VARCHAR2(10), ORG_CODE VARCHAR2(50), QUOTA_CODE NVARCHAR2(50), QUOTA_NAME VARCHAR2(50), QUOTA_DATE INTEGER, QUOTA_VALUE NUMBER ) ; comment on table T_ODS_DATA_3 is '指标数据表3'; comment on column T_ODS_DATA_3.COURIER_CODE is '快递员工号'; comment on column T_ODS_DATA_3.ORG_CODE is '部门编码'; comment on column T_ODS_DATA_3.QUOTA_CODE is '指标编码'; comment on column T_ODS_DATA_3.QUOTA_NAME is '指标名称'; comment on column T_ODS_DATA_3.QUOTA_DATE is '日期'; comment on column T_ODS_DATA_3.QUOTA_VALUE is '指标值'; create table T_ORG_DEPARTMENT ( ID NUMBER(10) not null, DEPTCODE VARCHAR2(128), DEPTNAME VARCHAR2(128), PRINCIPAL VARCHAR2(80), PHONE VARCHAR2(500), FAX VARCHAR2(500), PARENTID NUMBER(10), COMPANYNAME VARCHAR2(256), ZIPCODE VARCHAR2(50), ADDRESS VARCHAR2(1000), STATUS NUMBER(2), VALIDDATE DATE, INVALIDDATE DATE, DISPLAYORDER NUMBER(8), DEPTLEVEL NUMBER(2), DEPTDESC VARCHAR2(512), DEPTSEQ VARCHAR2(512), CREATEUSER VARCHAR2(64), CREATEDATE TIMESTAMP(6), MODIFYUSER VARCHAR2(64), MODIFYDATE TIMESTAMP(6), LEAF NUMBER(2), DEPTSHORTNAME VARCHAR2(200), PKORGCOMPANY NUMBER(10), DEPTSTANDCODE VARCHAR2(30), DEPTATTRIBUTE VARCHAR2(128), FINDEPTNAME VARCHAR2(128), FINDEPTCODE VARCHAR2(128), AREACODE VARCHAR2(64), DEPTPROVINCE VARCHAR2(64), DEPTCITY VARCHAR2(64), DEPTCOUNTRY VARCHAR2(64), DEPTEMAIL VARCHAR2(128), CANCELEDSYSTEMS VARCHAR2(1000), EHRDEPTCODE VARCHAR2(64), ISCAREERDEPT CHAR(1), ISBIGAREA CHAR(1), ISSMALLAREA CHAR(1), ACTIVE CHAR(1), BEGINTIME TIMESTAMP(6), ENDTIME TIMESTAMP(6), AIR_DISPATCH CHAR(1), ARRANGE_BIZ_TYPE VARCHAR2(50), ARRANGE_OUTFIELD VARCHAR2(50), BILLING_GROUP CHAR(1), COMPLEMENT_SIMPLE_NAME VARCHAR2(50), COUNTRY_REGION VARCHAR2(200), DELIVER_OUTFIELD VARCHAR2(50), DEP_COORDINATE VARCHAR2(50), DEPT_AREA NUMBER(8,3), DISPATCH_TEAM CHAR(1), DO_AIR_DISPATCH CHAR(1), EXPRESS_BIG_REGION CHAR(1), EXPRESS_BRANCH CHAR(1), EXPRESS_PART CHAR(1), EXPRESS_SALES_DEPARTMENT CHAR(1), EXPRESS_SORTING CHAR(1), IS_ARRANGE_GOODS CHAR(1), IS_DELIVER_SCHEDULE CHAR(1), IS_ENTITY_FINANCE CHAR(1), ISSECURITY CHAR(1), PINYIN VARCHAR2(200), SALES_DEPARTMENT CHAR(1), TRANS_DEPARTMENT CHAR(1), TRANS_TEAM CHAR(1), TRANSFER_CENTER CHAR(1), FLAYERTYPEID VARCHAR2(44), DIVISION_CODE VARCHAR2(50), DEPTATTRIBUTENO NUMBER(22), ORG_COMPANY_CODE VARCHAR2(128), PARENT_ORG_CODE VARCHAR2(128), PARENT_ORG_NAME VARCHAR2(128), PARENT_ORG_STANDCODE VARCHAR2(30), IS_MANAGE_DEPARTMENT CHAR(1), BUSINESSLINE VARCHAR2(30) ) ; comment on column T_ORG_DEPARTMENT.ID is 'UUMS主键'; comment on column T_ORG_DEPARTMENT.DEPTCODE is '组织编码'; comment on column T_ORG_DEPARTMENT.DEPTNAME is '部门名字'; comment on column T_ORG_DEPARTMENT.PRINCIPAL is '组织负责人工号'; comment on column T_ORG_DEPARTMENT.PHONE is '部门联系电话'; comment on column T_ORG_DEPARTMENT.FAX is '部门传真'; comment on column T_ORG_DEPARTMENT.PARENTID is '上级部门ID'; comment on column T_ORG_DEPARTMENT.COMPANYNAME is '所属子公司名称'; comment on column T_ORG_DEPARTMENT.ZIPCODE is '邮编号码'; comment on column T_ORG_DEPARTMENT.ADDRESS is '部门地址'; comment on column T_ORG_DEPARTMENT.STATUS is '组织状态'; comment on column T_ORG_DEPARTMENT.VALIDDATE is '(待废弃)启用日期'; comment on column T_ORG_DEPARTMENT.INVALIDDATE is '(待废弃)作废日期'; comment on column T_ORG_DEPARTMENT.DISPLAYORDER is '(待废弃)显示顺序'; comment on column T_ORG_DEPARTMENT.DEPTLEVEL is '部门层级'; comment on column T_ORG_DEPARTMENT.DEPTDESC is '部门描述'; comment on column T_ORG_DEPARTMENT.DEPTSEQ is '部门所有上级组织的ID（以.间隔）'; comment on column T_ORG_DEPARTMENT.CREATEUSER is '创建人'; comment on column T_ORG_DEPARTMENT.CREATEDATE is '创建时间'; comment on column T_ORG_DEPARTMENT.MODIFYUSER is '最后修改人'; comment on column T_ORG_DEPARTMENT.MODIFYDATE is '最后修改时间'; comment on column T_ORG_DEPARTMENT.LEAF is '是否为叶子节点'; comment on column T_ORG_DEPARTMENT.DEPTSHORTNAME is '部门简称'; comment on column T_ORG_DEPARTMENT.PKORGCOMPANY is '(待废弃)所属子公司主键'; comment on column T_ORG_DEPARTMENT.DEPTSTANDCODE is '组织标杆编码'; comment on column T_ORG_DEPARTMENT.DEPTATTRIBUTE is 'EHR组织性质'; comment on column T_ORG_DEPARTMENT.FINDEPTNAME is '(待废弃)所属成本中心'; comment on column T_ORG_DEPARTMENT.FINDEPTCODE is '(待废弃)成本中心编码'; comment on column T_ORG_DEPARTMENT.AREACODE is '(待废弃)地区编码默认拼音'; comment on column T_ORG_DEPARTMENT.DEPTPROVINCE is '省份编码'; comment on column T_ORG_DEPARTMENT.DEPTCITY is '城市编码'; comment on column T_ORG_DEPARTMENT.DEPTCOUNTRY is '区县编码'; comment on column T_ORG_DEPARTMENT.DEPTEMAIL is '组织邮箱'; comment on column T_ORG_DEPARTMENT.CANCELEDSYSTEMS is '已封存系统'; comment on column T_ORG_DEPARTMENT.EHRDEPTCODE is '(待废弃)EHR部门编码'; comment on column T_ORG_DEPARTMENT.ISCAREERDEPT is '是否事业部'; comment on column T_ORG_DEPARTMENT.ISBIGAREA is '是否营业大区'; comment on column T_ORG_DEPARTMENT.ISSMALLAREA is '是否营业小区'; comment on column T_ORG_DEPARTMENT.ACTIVE is '是否启用'; comment on column T_ORG_DEPARTMENT.BEGINTIME is '开始时间'; comment on column T_ORG_DEPARTMENT.ENDTIME is '结束时间'; comment on column T_ORG_DEPARTMENT.AIR_DISPATCH is '是否空运总调'; comment on column T_ORG_DEPARTMENT.ARRANGE_BIZ_TYPE is '理货业务类型'; comment on column T_ORG_DEPARTMENT.ARRANGE_OUTFIELD is '理货部门服务外场组织编码'; comment on column T_ORG_DEPARTMENT.BILLING_GROUP is '是否集中开单组'; comment on column T_ORG_DEPARTMENT.COMPLEMENT_SIMPLE_NAME is '补码简称'; comment on column T_ORG_DEPARTMENT.COUNTRY_REGION is '国家地区'; comment on column T_ORG_DEPARTMENT.DELIVER_OUTFIELD is '派送排单服务外场组织编码'; comment on column T_ORG_DEPARTMENT.DEP_COORDINATE is '部门服务区坐标编号'; comment on column T_ORG_DEPARTMENT.DEPT_AREA is '部门面积'; comment on column T_ORG_DEPARTMENT.DISPATCH_TEAM is '是否车队调度组'; comment on column T_ORG_DEPARTMENT.DO_AIR_DISPATCH is '是否可空运配载'; comment on column T_ORG_DEPARTMENT.EXPRESS_BIG_REGION is '是否快递大区'; comment on column T_ORG_DEPARTMENT.EXPRESS_BRANCH is '是否快递分部'; comment on column T_ORG_DEPARTMENT.EXPRESS_PART is '是否快递点部'; comment on column T_ORG_DEPARTMENT.EXPRESS_SALES_DEPARTMENT is '是否快递虚拟营业部'; comment on column T_ORG_DEPARTMENT.EXPRESS_SORTING is '是否快递分拣'; comment on column T_ORG_DEPARTMENT.IS_ARRANGE_GOODS is '是否理货'; comment on column T_ORG_DEPARTMENT.IS_DELIVER_SCHEDULE is '是否派送排单'; comment on column T_ORG_DEPARTMENT.IS_ENTITY_FINANCE is '是否实体财务部'; comment on column T_ORG_DEPARTMENT.ISSECURITY is '是否保安组'; comment on column T_ORG_DEPARTMENT.PINYIN is '组织拼音'; comment on column T_ORG_DEPARTMENT.SALES_DEPARTMENT is '是否营业部派送部'; comment on column T_ORG_DEPARTMENT.TRANS_DEPARTMENT is '是否车队'; comment on column T_ORG_DEPARTMENT.TRANS_TEAM is '是否车队组'; comment on column T_ORG_DEPARTMENT.TRANSFER_CENTER is '是否外场'; comment on column T_ORG_DEPARTMENT.FLAYERTYPEID is '财务部门性质'; comment on column T_ORG_DEPARTMENT.DIVISION_CODE is '事业部编码'; comment on column T_ORG_DEPARTMENT.DEPTATTRIBUTENO is '组织性质编码'; comment on column T_ORG_DEPARTMENT.ORG_COMPANY_CODE is '所属子公司编码'; comment on column T_ORG_DEPARTMENT.PARENT_ORG_CODE is '上级组织编码'; comment on column T_ORG_DEPARTMENT.PARENT_ORG_NAME is '上级组织名称'; comment on column T_ORG_DEPARTMENT.PARENT_ORG_STANDCODE is '上级组织标杆编码'; comment on column T_ORG_DEPARTMENT.IS_MANAGE_DEPARTMENT is '是否经营本部'; comment on column T_ORG_DEPARTMENT.BUSINESSLINE is '组织业务线'; alter table T_ORG_DEPARTMENT add constraint UK_ORG_DEPTID unique (ID); create index IDX_DEPTCODE on T_ORG_DEPARTMENT (DEPTCODE); create index IDX_DEPTSTANDCODE on T_ORG_DEPARTMENT (DEPTSTANDCODE); create index IDX_DEPT_PARENT_ORG_CODE on T_ORG_DEPARTMENT (PARENT_ORG_CODE); create table T_ORG_EMPLOYEE ( ID NUMBER(10), DEPTID NUMBER(10), EMPCODE VARCHAR2(64), EMPNAME VARCHAR2(64), GENDER NUMBER(1), POSITION VARCHAR2(128), BIRTHDATE DATE, STATUS NUMBER(2), INDATE DATE, OUTDATE DATE, OTEL VARCHAR2(100), OADDRESS VARCHAR2(256), OZIPCODE VARCHAR2(10), OEMAIL VARCHAR2(128), MOBILENO VARCHAR2(32), HTEL VARCHAR2(32), HADDRESS VARCHAR2(256), HZIPCODE VARCHAR2(10), PEMAIL VARCHAR2(128), WORKEXP VARCHAR2(512), REMARK VARCHAR2(512), CREATEUSER VARCHAR2(64), CREATEDATE TIMESTAMP(6), MODIFYUSER VARCHAR2(64), MODIFYDATE TIMESTAMP(6), PKEHRPOSITION NUMBER(10), PKBASPOSITION NUMBER(10), DEPTSTANDCODE VARCHAR2(30), DEPTNAME VARCHAR2(128), DOCTYPE VARCHAR2(64), DOCNUMBER VARCHAR2(64), ISTEMPEMP NUMBER(1), WITHCODE NUMBER(10,2), WAISTCODE NUMBER(10,2), HEIGHT NUMBER(10,2), VALIDDATE DATE, INVALIDDATE DATE, POSITIONGRADE VARCHAR2(64), WEIGHT NUMBER(10,2), BEFOREDEPTSTANDCODE VARCHAR2(30), BEFOREPKEHRPOSITION NUMBER(10), BEGINTIME TIMESTAMP(6), ENDTIME TIMESTAMP(6), JOBCODE VARCHAR2(32), NATIONALITY VARCHAR2(64), EDUCATION VARCHAR2(64), SCHOOL VARCHAR2(64), MARITAL VARCHAR2(64), CHARACTERRPR VARCHAR2(64), PINYIN VARCHAR2(200), DEPT_CODE VARCHAR2(64), ACTIVE CHAR(1) ) ; comment on column T_ORG_EMPLOYEE.ID is '主键'; comment on column T_ORG_EMPLOYEE.DEPTID is '主机构编码'; comment on column T_ORG_EMPLOYEE.EMPCODE is '职员编号'; comment on column T_ORG_EMPLOYEE.EMPNAME is '人员姓名'; comment on column T_ORG_EMPLOYEE.GENDER is '性别'; comment on column T_ORG_EMPLOYEE.POSITION is '职位'; comment on column T_ORG_EMPLOYEE.BIRTHDATE is '出生日期'; comment on column T_ORG_EMPLOYEE.STATUS is '人员状态'; comment on column T_ORG_EMPLOYEE.INDATE is '入职日期'; comment on column T_ORG_EMPLOYEE.OUTDATE is '离职日期'; comment on column T_ORG_EMPLOYEE.OTEL is '办公电话'; comment on column T_ORG_EMPLOYEE.OADDRESS is '办公地址'; comment on column T_ORG_EMPLOYEE.OZIPCODE is '(待废弃)办公邮编'; comment on column T_ORG_EMPLOYEE.OEMAIL is '办公邮件'; comment on column T_ORG_EMPLOYEE.MOBILENO is '手机号码'; comment on column T_ORG_EMPLOYEE.HTEL is '家庭电话'; comment on column T_ORG_EMPLOYEE.HADDRESS is '家庭地址'; comment on column T_ORG_EMPLOYEE.HZIPCODE is '(待废弃)家庭邮编'; comment on column T_ORG_EMPLOYEE.PEMAIL is '私人电子邮箱'; comment on column T_ORG_EMPLOYEE.WORKEXP is '工作描述'; comment on column T_ORG_EMPLOYEE.REMARK is '(待废弃)备注'; comment on column T_ORG_EMPLOYEE.CREATEUSER is '创建人'; comment on column T_ORG_EMPLOYEE.CREATEDATE is '创建时间'; comment on column T_ORG_EMPLOYEE.MODIFYUSER is '最后修改人'; comment on column T_ORG_EMPLOYEE.MODIFYDATE is '最后修改时间'; comment on column T_ORG_EMPLOYEE.PKEHRPOSITION is '对应EHR岗位的ID'; comment on column T_ORG_EMPLOYEE.PKBASPOSITION is '对应标准职位ID'; comment on column T_ORG_EMPLOYEE.DEPTSTANDCODE is '所属组织标杆编码'; comment on column T_ORG_EMPLOYEE.DEPTNAME is '所属组织名称'; comment on column T_ORG_EMPLOYEE.DOCTYPE is '(待废弃)证件类型'; comment on column T_ORG_EMPLOYEE.DOCNUMBER is '证件号码'; comment on column T_ORG_EMPLOYEE.ISTEMPEMP is '是否临时人员'; comment on column T_ORG_EMPLOYEE.WITHCODE is '上装码'; comment on column T_ORG_EMPLOYEE.WAISTCODE is '裤腰码'; comment on column T_ORG_EMPLOYEE.HEIGHT is '身高'; comment on column T_ORG_EMPLOYEE.VALIDDATE is '(待废弃)生效日期'; comment on column T_ORG_EMPLOYEE.INVALIDDATE is '(待废弃)失效日期'; comment on column T_ORG_EMPLOYEE.POSITIONGRADE is '职级'; comment on column T_ORG_EMPLOYEE.WEIGHT is '体重'; comment on column T_ORG_EMPLOYEE.BEGINTIME is '开始时间'; comment on column T_ORG_EMPLOYEE.ENDTIME is '结束时间'; comment on column T_ORG_EMPLOYEE.JOBCODE is '岗位编码'; comment on column T_ORG_EMPLOYEE.NATIONALITY is '民族'; comment on column T_ORG_EMPLOYEE.EDUCATION is '学历'; comment on column T_ORG_EMPLOYEE.SCHOOL is '毕业院校'; comment on column T_ORG_EMPLOYEE.MARITAL is '婚姻状况'; comment on column T_ORG_EMPLOYEE.CHARACTERRPR is '户口性质'; comment on column T_ORG_EMPLOYEE.PINYIN is '雇员名拼音'; comment on column T_ORG_EMPLOYEE.DEPT_CODE is '部门编码'; comment on column T_ORG_EMPLOYEE.ACTIVE is '是否启用'; alter table T_ORG_EMPLOYEE add constraint PK_ORG_EMPID primary key (ID); alter table T_ORG_EMPLOYEE add constraint UK_ORG_EMPCODE unique (EMPCODE); alter table T_ORG_EMPLOYEE add constraint NK_ORG_EMPID check ("ID" IS NOT NULL); create index IDX_EMPLOYEE_DEPT_CODE on T_ORG_EMPLOYEE (DEPT_CODE); create table T_PDAM_POSITION ( ORDERID NUMBER(15) not null, ID VARCHAR2(40) not null, USERCODE VARCHAR2(10) not null, LONGITUDE VARCHAR2(20) not null, LATITUDE VARCHAR2(20) not null, POSITIONTIME DATE not null, INSERTTIME DATE not null, UUID VARCHAR2(40) not null, IS_STOP CHAR(1), STOP_DURATION NUMBER, MOD_CNT NUMBER, MODIFYDATE DATE ) ; comment on table T_PDAM_POSITION is 'pdam经纬度数据表'; comment on column T_PDAM_POSITION.ORDERID is '传入数据的主键，区分每条数据'; comment on column T_PDAM_POSITION.ID is '区分不同的传入数据，非业务key'; comment on column T_PDAM_POSITION.USERCODE is '工号'; comment on column T_PDAM_POSITION.LONGITUDE is '经度'; comment on column T_PDAM_POSITION.LATITUDE is '纬度'; comment on column T_PDAM_POSITION.POSITIONTIME is '在此经纬度位置的时间'; comment on column T_PDAM_POSITION.INSERTTIME is '数据入库时间'; comment on column T_PDAM_POSITION.UUID is '传入参数uuid'; comment on column T_PDAM_POSITION.IS_STOP is '是否停留,Y:是,N:否'; comment on column T_PDAM_POSITION.STOP_DURATION is '停留时长(单位:分钟)'; comment on column T_PDAM_POSITION.MOD_CNT is '修改次数'; comment on column T_PDAM_POSITION.MODIFYDATE is '修改时间'; alter table T_PDAM_POSITION add primary key (ORDERID); create table T_SRV_ADDRESSCOLLECTION ( ID VARCHAR2(50) not null, BILL_NO VARCHAR2(50), DRIVER_CODE VARCHAR2(50), DRIVER_DEPT VARCHAR2(50), ADDRESS_TYPE VARCHAR2(50), GPS_LONGITUDE VARCHAR2(50), GPS_LATITUDE VARCHAR2(50), COLLECTION_TIME TIMESTAMP(6), SCOPEOORDINATES_ID VARCHAR2(50), CREATEDATE TIMESTAMP(6), CREATEUSER VARCHAR2(50), MODIFYDATE TIMESTAMP(6), MODIFYUSER VARCHAR2(50), IS_STOP CHAR(1), STOP_DURATION NUMBER, MOD_CNT NUMBER ) ; comment on table T_SRV_ADDRESSCOLLECTION is 'FOSS地址信息采集'; comment on column T_SRV_ADDRESSCOLLECTION.ID is 'ID主键'; comment on column T_SRV_ADDRESSCOLLECTION.BILL_NO is '运单号/订单号'; comment on column T_SRV_ADDRESSCOLLECTION.DRIVER_CODE is '司机编码'; comment on column T_SRV_ADDRESSCOLLECTION.DRIVER_DEPT is '司机所在组织'; comment on column T_SRV_ADDRESSCOLLECTION.ADDRESS_TYPE is '地址类型'; comment on column T_SRV_ADDRESSCOLLECTION.GPS_LONGITUDE is 'GPS经度'; comment on column T_SRV_ADDRESSCOLLECTION.GPS_LATITUDE is 'GPS纬度'; comment on column T_SRV_ADDRESSCOLLECTION.COLLECTION_TIME is '采集时间'; comment on column T_SRV_ADDRESSCOLLECTION.SCOPEOORDINATES_ID is '区域范围ID'; comment on column T_SRV_ADDRESSCOLLECTION.CREATEDATE is '创建时间'; comment on column T_SRV_ADDRESSCOLLECTION.CREATEUSER is '创建人'; comment on column T_SRV_ADDRESSCOLLECTION.MODIFYDATE is '最后更改时间'; comment on column T_SRV_ADDRESSCOLLECTION.MODIFYUSER is '最后更改人'; comment on column T_SRV_ADDRESSCOLLECTION.IS_STOP is '是否停留,Y:是,N:否'; comment on column T_SRV_ADDRESSCOLLECTION.STOP_DURATION is '停留时长(单位:分钟)'; comment on column T_SRV_ADDRESSCOLLECTION.MOD_CNT is '修改次数'; alter table T_SRV_ADDRESSCOLLECTION add primary key (ID); create index IDX_SRV_COLLECTION_TIME on T_SRV_ADDRESSCOLLECTION (COLLECTION_TIME); create table T_SYS_CONFIG ( ID VARCHAR2(50) not null, CONFIG_KEY VARCHAR2(128) not null, CONFIG_VALUE VARCHAR2(128), ACTIVE VARCHAR2(8), CREATE_USER VARCHAR2(50), CREATE_DATE DATE, MODIFY_USER VARCHAR2(50), MODIFY_DATE DATE ) ; alter table T_SYS_CONFIG add primary key (ID); create table T_TEST ( LINE VARCHAR2(64), TEXT VARCHAR2(4000) ) ; create sequence SEQ_T_EVS_FEEDBACK minvalue 1 maxvalue 999999 start with 520 increment by 1 nocache cycle; create or replace view v_courier_schedule as select sc.SMALL_REGIONS_CODE, sc.COURIER_CODE, to_char(sc.schedule_date, 'yyyymmdd') as pro_date from t_bas_courier_schedule sc where sc.active = 'Y' and (lower(sc.plan_type) in ('work', 'duty', 'training') or upper(sc.plan_type) in ('WORK', 'DUTY', 'TRAINIG')); create or replace view v_emp_courier as select ID,DEPTID,EMPCODE,EMPNAME,GENDER,POSITION,BIRTHDATE,STATUS,INDATE,OUTDATE,OTEL,OADDRESS,OZIPCODE,OEMAIL,MOBILENO,HTEL,HADDRESS,HZIPCODE,PEMAIL,WORKEXP,REMARK,CREATEUSER,CREATEDATE,MODIFYUSER,MODIFYDATE,PKEHRPOSITION,PKBASPOSITION,DEPTSTANDCODE,DEPTNAME,DOCTYPE,DOCNUMBER,ISTEMPEMP,WITHCODE,WAISTCODE,HEIGHT,VALIDDATE,INVALIDDATE,POSITIONGRADE,WEIGHT,BEFOREDEPTSTANDCODE,BEFOREPKEHRPOSITION,BEGINTIME,ENDTIME,JOBCODE,NATIONALITY,EDUCATION,SCHOOL,MARITAL,CHARACTERRPR,PINYIN,DEPT_CODE AS DEPTCODE,ACTIVE from t_org_employee e where e.active = 'Y' and (e.position = '快递员' or e.position = '快递员组长');//存储过程开始 create or replace procedure p_evs_courier_bigzone(v_product_date in varchar2) as v_cnt varchar2(6); /*每一个编码对应的快递员人数*/ begin /*计算大区的编码,大区所有快递员人数*/ /*删除v_product_date数据方便以后重新跑数*/ delete from t_evs_courier_bigzone bi where bi.product_date = v_product_date; /*新增数据id,code,product_date*/ insert into t_evs_courier_bigzone (id, code, product_date) select sys_guid(), o.DEPTCODE, v_product_date from T_ORG_DEPARTMENT o where o.EXPRESS_BIG_REGION = 'Y' and o.active = 'Y'; /*更新courier_number*/ for c in (select code from t_evs_courier_bigzone) loop select count(1) into v_cnt from v_emp_courier e where e.deptcode in (select o.DEPTCODE from T_ORG_DEPARTMENT o where o.active = 'Y' start with o.parent_org_code = c.code connect by prior o.DEPTCODE = o.parent_org_code); update t_evs_courier_bigzone bi set bi.courier_number = v_cnt where bi.code = c.code and bi.product_date = v_product_date; end loop; commit; exception when others then dbms_output.put_line(sqlerrm); rollback; end p_evs_courier_bigzone; / create or replace procedure p_evs_courier_cumulativedata(v_product_date in varchar2) as v_year_month varchar2(6); /*统计的日期 年月*/ v_year_month_first varchar2(8); /*当月第一天*/ begin select to_char(to_date(v_product_date, 'yyyymmdd'), 'yyyymm') into v_year_month from dual; select to_char(trunc(to_date(v_product_date, 'yyyymmdd'), 'mm'), 'yyyymmdd') into v_year_month_first from dual; /*删除当天数据,以后可以重新刷数据*/ delete from t_evs_courier_cumulativedata cu where cu.product_date = v_product_date; /*插入 快递员当月累计数据 数据(除了 快递员工作日天数, 日均派件票数,缺勤天数)*/ insert into t_evs_courier_cumulativedata (id, product_date, courier_code, courier_name, income, urge_receive, urge_send, mistake, complain, traffic_accident, contraband, lost_goods, abnormal_sign, praise, create_date) select sys_guid() as id, v_product_date as v_product_date, e.empcode as courier_code, e.empname as courier_name, nvl(sum(da.income), 0) as income, nvl(sum(da.urge_receive), 0) as urge_receive, nvl(sum(da.urge_send), 0) as urge_send, nvl(sum(da.mistake), 0) as mistake, nvl(sum(da.complain), 0) as complain, nvl(sum(da.traffic_accident), 0) as traffic_accident, nvl(sum(da.contraband), 0) as contraband, nvl(sum(da.lost_goods), 0) as lost_goods, nvl(sum(da.abnormal_sign), 0) as abnormal_sign, nvl(sum(da.praise), 0) as praise, sysdate as create_date from v_emp_courier e left join t_evs_courier_data da on (da.courier_code = e.empcode and da.product_date between v_year_month_first and v_product_date) group by e.empcode, e.empname; /*计算 快递员工作日天数*/ update t_evs_courier_cumulativedata cu set cu.work_days = (select count(distinct sc.courier_code || sc.pro_date) from v_courier_schedule sc where sc.pro_date between v_year_month_first and v_product_date and sc.courier_code = cu.courier_code) where cu.product_date = v_product_date; /*计算 缺勤天数*/ update t_evs_courier_cumulativedata cu set cu.absence_days = (select case when cu.work_days >= att.attendance_days then 0 when cu.work_days < att.attendance_days then (case when to_char(e.indate, 'yyyymm') = v_year_month then (select count(1) - cu.work_days from t_evs_courier_attendance att where att.is_work_day = 'Y' and att.date_time between to_char(e.indate, 'yyyymmdd') and v_product_date) /*当月为新员工*/ else att.attendance_days - cu.work_days end) else null end from t_evs_courier_attendance att, t_org_employee e where att.date_time = v_product_date and e.active = 'Y' and e.empcode = cu.courier_code) where cu.product_date = v_product_date; /*计算 日均派件票数*/ update t_evs_courier_cumulativedata cu set cu.send_efficiency = (select round(decode(cu.work_days, 0, 0, sum(da.send) / cu.work_days), 2) from t_evs_courier_data da where da.product_date between v_year_month_first and v_product_date and da.courier_code = cu.courier_code) where cu.product_date = v_product_date; commit; exception when others then dbms_output.put_line(sqlerrm); rollback; end p_evs_courier_cumulativedata; / create or replace procedure p_evs_courier_data(v_product_date in varchar2) as begin for c in (select da.id, da.courier_code from t_evs_courier_data da where da.product_date = v_product_date) loop /*更新快递员姓名*/ update t_evs_courier_data d set d.courier_name = (select e.empname from t_org_employee e where e.active = 'Y' and e.empcode = c.courier_code) where d.id = c.id; /*计算 是否快递员工作日*/ update t_evs_courier_data da set da.is_work_day = (select case when count(1) > 0 then 'Y' else 'N' end from v_courier_schedule sc where sc.pro_date = v_product_date and sc.courier_code = c.courier_code) where da.id = c.id; /*更新 交通事故,丢货,表扬*/ update t_evs_courier_data da set (da.traffic_accident, da.lost_goods, da.praise) = (select nvl(up.traffic_accident, 0), nvl(up.lost_goods, 0), nvl(up.praise, 0) from t_evs_courier_data_upload up where up.product_date = v_product_date and up.courier_code = c.courier_code) where da.id = c.id; end loop; commit; exception when others then dbms_output.put_line(sqlerrm); rollback; end p_evs_courier_data; / create or replace procedure p_evs_courier_data_tongbu(v_product_date in varchar2) as begin delete from t_evs_courier_data where PRODUCT_DATE = v_product_date; commit; insert into t_evs_courier_data (ID, PRODUCT_DATE, --日期 COURIER_CODE, -- 快递员工号 --COURIER_NAME, --快递员姓名 RECEIVE, --收件票数 SEND, --派送票数 INCOME, --收入 COMPLAIN, --投诉数 URGE_RECEIVE, --催收票数 URGE_SEND, -- 催派票数 MISTAKE, --差错 CONTRABAND, --违禁品 ABNORMAL_SIGN --异常签收 ) select sys_guid(), M.PRODUCT_DATE, --日期 nvl(M.COURIER_CODE, '空'), -- 快递员工号 --nvl(M.COURIER_NAME, '空'), --快递员姓名 sum(M.RECEIVE), --收件票数 sum(M.SEND), --派送票数 sum(M.INCOME), --收入 sum(M.COMPLAIN), --投诉数 sum(M.URGE_RECEIVE), --催收票数 sum(M.URGE_SEND), -- 催派票数 sum(M.MISTAKE), --差错 sum(M.CONTRABAND), --违禁品 sum(M.ABNORMAL_SIGN) --异常签收 from (select to_char(w.calendar_date, 'yyyymmdd') PRODUCT_DATE, --日期 w.EMP_CODE COURIER_CODE, -- 快递员工号 --w.EMP_NAME COURIER_NAME, --快递员姓名 w.RECEIPT_NUM RECEIVE, --收件票数 w.SENDTICKET_NUM SEND, --派送票数 w.TICKET_AMOUNT INCOME, --收入 0 COMPLAIN, --投诉数 0 URGE_RECEIVE, --催收票数 0 URGE_SEND, -- 催派票数 0 MISTAKE, --差错 0 CONTRABAND, --违禁品 0 ABNORMAL_SIGN --异常签收 from t_EVS_WAYBILLTICKET w where to_char(w.calendar_date, 'yyyymmdd') = v_product_date union all select to_char(p.calendar_date, 'yyyymmdd') PRODUCT_DATE, --日期 p.EMP_CODE COURIER_CODE, -- 快递员工号 --p.EMP_NAME COURIER_NAME, --快递员姓名 0 RECEIVE, --收件票数 0 SEND, --派送票数 0 INCOME, --收入 p.COMPLAINTS_NUM COMPLAIN, --投诉数 p.COLLECTION_NUM URGE_RECEIVE, --催收票数 p.RUSHSEND_NUM URGE_SEND, -- 催派票数 p.ERROR_NUM MISTAKE, --差错 p.CONTRABAND_NUM CONTRABAND, --违禁品 p.ABNORMAL_NUM ABNORMAL_SIGN --异常签收 from t_EVS_ERROR_PICKTICKET p where to_char(p.calendar_date, 'yyyymmdd') = v_product_date) M where M.COURIER_CODE is not null group by M.PRODUCT_DATE, --日期 nvl(M.COURIER_CODE, '空') -- 快递员工号 ; commit; update t_evs_courier_data da set da.city_cate = (select p.citytype_name from T_EVS_EXP_PERSONCITY p where p.emp_code = da.courier_code) where da.product_date = v_product_date; commit; exception when others then dbms_output.put_line(sqlerrm); rollback; end p_evs_courier_data_tongbu; / create or replace procedure p_evs_courier_data_warning(v_product_date in varchar2) as v_year_month_day varchar2(128); /* YYYY年MM月DD日 */ begin select to_char(to_date(v_product_date, 'yyyymmdd'), 'YYYY"年"MM"月"DD"日"') into v_year_month_day from dual; /*删除当天数据,以后可以重新刷数据*/ delete from t_evs_courier_data_warning wa where wa.product_date = v_product_date; /*插入 排名位于大区的后10% 数据*/ for c in (select bi.code, bi.courier_number from t_evs_courier_bigzone bi where bi.product_date = v_product_date) loop insert into t_evs_courier_data_warning select sys_guid(), sc.courier_code, v_product_date, '截止' || v_year_month_day || '您的星级考核积分排名位于大区的后10%，请重点关注！', sysdate from t_evs_courier_score sc join t_evs_courier_bigzone bi on sc.big_region = bi.code where sc.big_region = c.code and sc.product_date = v_product_date and bi.product_date = v_product_date and sc.rank_big_region > bi.courier_number * 0.9 and bi.courier_number > 0; end loop; /*插入 [1]快递员催收票数、[2]快递员催派票数、[3]快递员投诉票数、 [4]快递员差错个数、[5]快递员异常签收票数、[6]快递员违禁品、拒收品差错个数、 [7]快递员缺勤天数、[8]快递员丢货票数 >0 数据*/ insert into t_evs_courier_data_warning select sys_guid(), da.courier_code, v_product_date, v_year_month_day || case when da.urge_receive > 0 then '您新增催收票数' || da.urge_receive || '票，本月催收票数累计' || cu.urge_receive || '票；' end || case when da.urge_send > 0 then '新增催派票数' || da.urge_send || '票，本月催派票数累计' || cu.urge_send || '票；' end || case when da.complain > 0 then '新增投诉票数' || da.complain || '票，本月投诉票数累计' || cu.complain || '票；' end || case when da.mistake > 0 then '新增差错个数' || da.mistake || '个，本月差错个数累计' || cu.mistake || '个；' end || case when da.lost_goods > 0 then '新增丢货票数' || da.lost_goods || '票，本月丢货票数累计' || cu.lost_goods || '票；' end || case when da.abnormal_sign > 0 then '新增异常签收票数' || da.abnormal_sign || '票，本月异常签收票数累计' || cu.abnormal_sign || '票；' end || case when da.contraband > 0 then '新增拒收品、违禁品差错个数' || da.contraband || '个，本月拒收品、违禁品差错个数累计' || cu.contraband || '个；' end || case when da.is_absence > 0 then '新增缺勤天数' || da.is_absence || '天，本月缺勤天数累计' || cu.absence_days || '天；' end || '请重点关注！', sysdate from t_evs_courier_data da join t_evs_courier_cumulativedata cu on (da.courier_code = cu.courier_code and da.product_date = cu.product_date) where da.product_date = v_product_date and nvl(da.complain, 0) + nvl(da.urge_receive, 0) + nvl(da.urge_send, 0) + nvl(da.mistake, 0) + nvl(da.contraband, 0) + nvl(da.lost_goods, 0) + nvl(da.abnormal_sign, 0) + nvl(da.is_absence, 0) > 0; commit; exception when others then dbms_output.put_line(sqlerrm); rollback; end p_evs_courier_data_warning; / create or replace procedure p_evs_courier_info(v_product_date in varchar2) as begin /*(1)删除当天数据 ,后面可以重新刷数据*/ delete from t_evs_courier_info sc where sc.product_date = v_product_date; /** 统计快递员 所属 经营本部,事业部,大区,小区,营业部(或者 点部) */ for c in (select v.empcode, v.deptcode from v_emp_courier v) loop insert into t_evs_courier_info with t as (select d.deptcode, d.deptname, d.deptlevel from t_org_department d where d.active = 'Y' start with d.deptcode = c.deptcode connect by nocycle prior d.parent_org_code = d.deptcode) select sys_guid(), sysdate, c.empcode, v_product_date, t3.deptcode, t3.deptname, t4.deptcode, t4.deptname, t5.deptcode, t5.deptname, t6.deptcode, t6.deptname, t7.deptcode, t7.deptname from (select t.deptcode, t.deptname from t where t.deptlevel = 3) t3, (select t.deptcode, t.deptname from t where t.deptlevel = 4) t4, (select t.deptcode, t.deptname from t where t.deptlevel = 5) t5, (select t.deptcode, t.deptname from t where t.deptlevel = 6) t6, (select t.deptcode, t.deptname from t where t.deptlevel = 7) t7; end loop; commit; exception when others then dbms_output.put_line(sqlerrm); dbms_output.put_line('**Error line: ' || dbms_utility.format_error_backtrace()); rollback; end p_evs_courier_info; / create or replace procedure p_evs_courier_score(v_product_date in varchar2) as v_num_7000 number; v_num_6000 number; v_num_13000 number; v_num_19000 number; v_num_12000 number; v_num_18000 number; begin v_num_7000 := 7000; v_num_6000 := 6000; v_num_13000 := 13000; v_num_19000 := 19000; v_num_12000 := 12000; v_num_18000 := 18000; /*(1)删除当天数据 ,后面可以重新刷数据*/ delete from t_evs_courier_score sc where sc.product_date = v_product_date; /*删除t_evs_exp_personcity表可能出现的重复快递员工号数据*/ begin for c in (select p.emp_code from t_evs_exp_personcity p group by p.emp_code having count(1) > 1) loop delete from t_evs_exp_personcity p where p.emp_code = c.emp_code and p.rowid <> (select max(p.rowid) from t_evs_exp_personcity p join t_org_employee e on (e.empcode = p.emp_code and e.empname = p.emp_name) where p.emp_code = c.emp_code); end loop; end; /*(2)插入快递员积分表 插入基础数据*/ insert into t_evs_courier_score (id, data_id, product_date, courier_code, courier_name, create_date) select sys_guid() as id, id as data_id, v_product_date as product_date, courier_code as courier_code, courier_name as courier_name, create_date as create_date from t_evs_courier_cumulativedata cu where cu.product_date = v_product_date; /*(3)更新 日均派件票数积分*/ update t_evs_courier_score sc set (sc.efficiency_score, sc.income_score, sc.urge_receive_score, sc.urge_send_score, sc.mistake_score, sc.complain_score, sc.accident_score, sc.contraband_score, sc.lost_score, sc.abnormal_score, sc.praise_score, sc.absence_score) = (select case when cu.send_efficiency = 0 then 0 when cu.send_efficiency >= 6 then 25 + 3 * ceil(cu.send_efficiency - 6) else 25 - 3 * ceil(6 - cu.send_efficiency) end, /*日均派件票数积分*/ case when cu.income = 0 then 0 when da.CITYTYPE_NAME = '特级城市' then (case when cu.income >= v_num_7000 then 35 + 5 * round((cu.income - v_num_7000) / 1000) else 35 - 5 * round((v_num_7000 - cu.income) / 1000) end) when (da.CITYTYPE_NAME <> '特级城市' or da.CITYTYPE_NAME is null) then (case when cu.income >= v_num_6000 then 35 + 5 * round((cu.income - v_num_6000) / 1000) else 35 - 5 * round((v_num_6000 - cu.income) / 1000) end) else 0 end, /*收入积分*/ case when da.CITYTYPE_NAME = '特级城市' then (case when cu.income < v_num_7000 then -10 * cu.urge_receive * 1.0 when (cu.income >= v_num_7000 and cu.income < v_num_13000) then -10 * cu.urge_receive * 0.95 when (cu.income >= v_num_13000 and cu.income < v_num_19000) then -10 * cu.urge_receive * 0.9 when cu.income >= v_num_19000 then -10 * cu.urge_receive * 0.85 else 0 end) when (da.CITYTYPE_NAME <> '特级城市' or da.CITYTYPE_NAME is null) then (case when cu.income < v_num_6000 then -10 * cu.urge_receive * 1.0 when (cu.income >= v_num_6000 and cu.income < v_num_12000) then -10 * cu.urge_receive * 0.95 when (cu.income >= v_num_12000 and cu.income < v_num_18000) then -10 * cu.urge_receive * 0.9 when cu.income >= v_num_18000 then -10 * cu.urge_receive * 0.85 else 0 end) else 0 end, /*催收个数积分*/ case when da.CITYTYPE_NAME = '特级城市' then (case when cu.income < v_num_7000 then -10 * cu.urge_send * 1.0 when (cu.income >= v_num_7000 and cu.income < v_num_13000) then -10 * cu.urge_send * 0.95 when (cu.income >= v_num_13000 and cu.income < v_num_19000) then -10 * cu.urge_send * 0.9 when cu.income >= v_num_19000 then -10 * cu.urge_send * 0.85 else 0 end) when (da.CITYTYPE_NAME <> '特级城市' or da.CITYTYPE_NAME is null) then (case when cu.income < v_num_6000 then -10 * cu.urge_send * 1.0 when (cu.income >= v_num_6000 and cu.income < v_num_12000) then -10 * cu.urge_send * 0.95 when (cu.income >= v_num_12000 and cu.income < v_num_18000) then -10 * cu.urge_send * 0.9 when cu.income >= v_num_18000 then -10 * cu.urge_send * 0.85 else 0 end) else 0 end, /*催派个数积分*/ case when da.CITYTYPE_NAME = '特级城市' then (case when cu.income < v_num_7000 then -15 * cu.mistake * 1.0 when (cu.income >= v_num_7000 and cu.income < v_num_13000) then -15 * cu.mistake * 0.95 when (cu.income >= v_num_13000 and cu.income < v_num_19000) then -15 * cu.mistake * 0.9 when cu.income >= v_num_19000 then -15 * cu.mistake * 0.85 else 0 end) when (da.CITYTYPE_NAME <> '特级城市' or da.CITYTYPE_NAME is null) then (case when cu.income < v_num_6000 then -15 * cu.mistake * 1.0 when (cu.income >= v_num_6000 and cu.income < v_num_12000) then -15 * cu.mistake * 0.95 when (cu.income >= v_num_12000 and cu.income < v_num_18000) then -15 * cu.mistake * 0.9 when cu.income >= v_num_18000 then -15 * cu.mistake * 0.85 else 0 end) else 0 end, /*差错积分*/ case when da.CITYTYPE_NAME = '特级城市' then (case when cu.income < v_num_7000 then -30 * cu.complain * 1.0 when (cu.income >= v_num_7000 and cu.income < v_num_13000) then -30 * cu.complain * 0.95 when (cu.income >= v_num_13000 and cu.income < v_num_19000) then -30 * cu.complain * 0.9 when cu.income >= v_num_19000 then -30 * cu.complain * 0.85 else 0 end) when (da.CITYTYPE_NAME <> '特级城市' or da.CITYTYPE_NAME is null) then (case when cu.income < v_num_6000 then -30 * cu.complain * 1.0 when (cu.income >= v_num_6000 and cu.income < v_num_12000) then -30 * cu.complain * 0.95 when (cu.income >= v_num_12000 and cu.income < v_num_18000) then -30 * cu.complain * 0.9 when cu.income >= v_num_18000 then -30 * cu.complain * 0.85 else 0 end) else 0 end, /*投诉积分*/ -20 * cu.traffic_accident, /*交通事故积分*/ -20 * cu.contraband, /*违禁品积分*/ -30 * cu.lost_goods, /*丢货积分*/ -5 * cu.abnormal_sign, /*异常签收积分*/ case when cu.praise <= 3 then 5 * cu.praise else 15 end, /*表扬积分*/ -10 * cu.absence_days /*缺勤天数积分*/ from t_evs_courier_cumulativedata cu left join t_evs_exp_personcity da on cu.courier_code = da.EMP_CODE where sc.data_id = cu.id) where sc.product_date = v_product_date; /*(4)更新 总积分*/ update t_evs_courier_score sc set sc.total_score = round(income_score + efficiency_score + urge_receive_score + urge_send_score + mistake_score + complain_score + accident_score + contraband_score + lost_score + praise_score + absence_score + abnormal_score, 2) where sc.product_date = v_product_date; /*(5)更新 快递员所属大区*/ for c in (select e.empcode, e.deptcode from v_emp_courier e) loop update t_evs_courier_score sc set sc.big_region = (select min(o.DEPTCODE) from T_ORG_DEPARTMENT o where o.EXPRESS_BIG_REGION = 'Y' and o.active = 'Y' start with o.DEPTCODE = c.deptcode connect by prior o.parent_org_code = o.DEPTCODE) where sc.courier_code = c.empcode and sc.product_date = v_product_date; end loop; /*(6)更新 快递员在大区的排名*/ for c in (select id, rank() over(partition by big_region order by nvl(total_score, 0) desc) rank from t_evs_courier_score where product_date = v_product_date) loop update t_evs_courier_score sc set sc.rank_big_region = c.rank where sc.id = c.id and sc.product_date = v_product_date; end loop; commit; exception when others then dbms_output.put_line(sqlerrm); rollback; end p_evs_courier_score; / create or replace procedure p_evs_day_and_month_data(v_product_date in varchar2) as v_year_month_first varchar2(8); /*当月第一天*/ begin select to_char(trunc(to_date(v_product_date, 'yyyymmdd'), 'mm'), 'yyyymmdd') into v_year_month_first from dual; /*(1)删除时间为v_product_date的数据,(如果存在时间为v_product_date的数据,数据重新算,防止重复数据存在)*/ delete from t_evs_orgdata_day d where d.product_date = v_product_date; delete from t_evs_orgdata_month m where m.product_date = v_product_date; /*(2)统计 小区 当日 数据*/ /*(2.1)插入小区数据:小区编码,小区名称,管理部门,组织类型(1:小区)*/ insert into t_evs_orgdata_day (id, org_type, org_code, org_name, product_date, parant_code, create_date) (select sys_guid() as id, '1' as org_type, sm.region_code as org_code, sm.region_name as org_name, v_product_date as product_date, d.deptcode as parant_code, sysdate from t_bas_express_smallzone sm join t_org_department d on sm.management = d.deptcode where d.active = 'Y' and sm.active = 'Y'); /*(2.2)根据 年月,日,小区编码 统计更新 排班表当日快递员人数 */ update t_evs_orgdata_day d set d.courier_num = (select count(*) from v_courier_schedule vcs where vcs.small_regions_code = d.org_code /*小区编码*/ and vcs.pro_date = v_product_date) where d.product_date = v_product_date and d.org_type = '1'; /*组织类型为小区*/ /*(2.3) 根据年月,日,小区编码 统计 收件票数,派送票数,收入,投诉数,催收票数,催派票数 */ for c in (select * from t_evs_orgdata_day d where d.product_date = v_product_date and d.org_type = '1') loop update t_evs_orgdata_day d set (d.receive, d.send, d.income, d.complain, d.urge_receive, d.urge_send) = (select nvl(sum(da.receive), 0), /*总收件票数*/ nvl(sum(da.send), 0), /*总派送票数*/ nvl(sum(da.income), 0), /*总收入*/ nvl(sum(da.complain), 0), /*总投诉数*/ nvl(sum(da.urge_receive), 0), /*总催收票数*/ nvl(sum(da.urge_send), 0) /*总催派票数*/ from t_evs_courier_data da join v_courier_schedule vcs on da.courier_code = vcs.courier_code where vcs.small_regions_code = c.org_code and da.product_date = v_product_date and vcs.pro_date = v_product_date) where d.id = c.id; end loop; /*(2.4) 根据年月,日,小区编码 统计 人均收件票数,人均派送票数,人均收入,人均投诉数,人均催收票数,人均催派票数,人均效率 */ update t_evs_orgdata_day d set d.avg_receive = round(d.receive / d.courier_num, 2), d.avg_send = round(d.send / d.courier_num, 2), d.avg_income = round(d.income / d.courier_num, 2), d.avg_complain = round(d.complain / d.courier_num, 2), d.avg_urge_receive = round(d.urge_receive / d.courier_num, 2), d.avg_urge_send = round(d.urge_send / d.courier_num, 2), d.avg_efficiency = round((d.receive + d.send) / d.courier_num, 2) where d.product_date = v_product_date /*生产日期*/ and d.org_type = '1' and d.courier_num > 0; /*快递员人数>0才更新,否则不更新*/ /*(2.5)人均收件票数,人均派送票数,人均收入,人均投诉数,人均催收票数,人均催派票数,人均效率. 快递员人数为0,更新为0*/ update t_evs_orgdata_day d set d.avg_receive = 0, d.avg_send = 0, d.avg_income = 0, d.avg_complain = 0, d.avg_urge_receive = 0, d.avg_urge_send = 0, d.avg_efficiency = 0 where d.product_date = v_product_date /*生产日期*/ and d.org_type = '1' and (d.courier_num = 0 or d.courier_num is null); /*快递员人数=0才更新,全部更新为0*/ /*(3)统计 小区 累积到当月当日当日 数据*/ /*(3.1)统计累计到当日当月数据*/ insert into t_evs_orgdata_month (id, org_type, org_code, org_name, product_date, parant_code, create_date) (select sys_guid() as id, '1' as org_type, sm.region_code as org_code, sm.region_name as org_name, v_product_date as product_date, d.deptcode as parant_code, sysdate from t_bas_express_smallzone sm join t_org_department d on sm.management = d.deptcode where d.active = 'Y' and sm.active = 'Y'); /*(3.2.1)统计当月当日 小区人数*/ update t_evs_orgdata_month d set d.courier_num = (select count(*) from v_courier_schedule vcs where vcs.small_regions_code = d.org_code /*小区编码*/ and vcs.pro_date = v_product_date) where d.product_date = v_product_date and d.org_type = '1'; /*组织类型为小区*/ /*(3.2.2)统计累计当月 小区人数*/ update t_evs_orgdata_month d set d.cnt_courier_num = (select count(*) from v_courier_schedule vcs where vcs.small_regions_code = d.org_code /*小区编码*/ and vcs.pro_date between v_year_month_first and v_product_date) where d.product_date = v_product_date and d.org_type = '1'; /*组织类型为小区*/ /*(3.3) 根据年月,日,小区编码 统计 收件票数,派送票数,收入,投诉数,催收票数,催派票数*/ update t_evs_orgdata_month d set (d.receive, d.send, d.income, d.complain, d.urge_receive, d.urge_send) = (select nvl(sum(da.receive), 0), /*总收件票数*/ nvl(sum(da.send), 0), /*总派送票数*/ nvl(sum(da.income), 0), /*总收入*/ nvl(sum(da.complain), 0), /*总投诉数*/ nvl(sum(da.urge_receive), 0), /*总催收票数*/ nvl(sum(da.urge_send), 0) /*总催派票数*/ from t_evs_courier_data da join v_courier_schedule vcs on (da.courier_code = vcs.courier_code and vcs.pro_date = da.product_date and da.product_date between v_year_month_first and v_product_date) where vcs.small_regions_code = d.org_code) where d.product_date = v_product_date and d.org_type = '1'; /*(3.4) 根据年月,日,小区编码 统计 人均收件票数,人均派送票数,人均收入,人均投诉数,人均催收票数,人均催派票数,人均效率 */ update t_evs_orgdata_month d set d.avg_receive = round(d.receive / d.courier_num, 2), d.avg_send = round(d.send / d.courier_num, 2), d.avg_income = round(d.income / d.courier_num, 2), d.avg_complain = round(d.complain / d.courier_num, 2), d.avg_urge_receive = round(d.urge_receive / d.courier_num, 2), d.avg_urge_send = round(d.urge_send / d.courier_num, 2), d.avg_efficiency = round((d.receive + d.send) / d.courier_num, 2) where d.product_date = v_product_date /*生产日期*/ and d.org_type = '1' and d.courier_num > 0; /*快递员人数>0才更新,否则不更新*/ /*(3.5)人均收件票数,人均派送票数,人均收入,人均投诉数,人均催收票数,人均催派票数,人均效率. 快递员人数为0,更新为0*/ update t_evs_orgdata_month d set d.avg_receive = 0, d.avg_send = 0, d.avg_income = 0, d.avg_complain = 0, d.avg_urge_receive = 0, d.avg_urge_send = 0, d.avg_efficiency = 0 where d.product_date = v_product_date /*生产日期*/ and d.org_type = '1' and (d.courier_num = 0 or d.courier_num is null); /*快递员人数=0才更新,全部更新为0*/ /*(3.6)更新 小区均分均值 */ update t_evs_orgdata_month m set m.avg_score = (select case when m.courier_num = 0 then 0 else round(nvl(sum(sc.total_score), 0) / m.courier_num, 0) end from t_evs_courier_score sc join v_courier_schedule vcs on (sc.courier_code = vcs.courier_code and sc.product_date = v_product_date and vcs.pro_date = v_product_date) where vcs.small_regions_code = m.org_code) where m.product_date = v_product_date and m.org_type = '1'; /*(4)统计 点部(营业部) 当日 数据*/ /*(4.1)当日数据 把 点部(营业部) 数据插入到 t_evs_orgdata_day 表 */ insert into t_evs_orgdata_day (id, product_date, org_type, org_code, org_name, parant_code, create_date) (select sys_guid() as id, v_product_date as product_date, '3' as org_type, t.org_code as org_code, t.org_name as org_name, t.parant_code as parant_code, sysdate from (select distinct d.deptcode as org_code, d.deptname as org_name, d.parent_org_code as parant_code from t_org_department d right join t_bas_express_smallzone sm on d.deptcode = sm.management where d.active = 'Y' and sm.active = 'Y') t); /*(4.2)根据 年月日 统计 组织表当日快递员人数 */ for c in (select d.id, d.org_code from t_evs_orgdata_day d where d.product_date = v_product_date and d.org_type = '3') loop update t_evs_orgdata_day d set d.courier_num = (select count(1) from v_emp_courier vec where vec.deptcode in (select c.org_code from dual union select de.deptcode from t_org_department de where de.active = 'Y' start with de.parent_org_code = c.org_code connect by prior de.deptcode = de.parent_org_code)) where d.id = c.id; /*组织类型为 点部(营业部) */ end loop; /*(4.3) 根据 年月,日,组织编码 统计 收件票数,派送票数,收入,投诉数,催收票数,催派票数*/ for c in (select d.id, d.org_code from t_evs_orgdata_day d where d.product_date = v_product_date and d.org_type = '3') loop update t_evs_orgdata_day d set (d.receive, d.send, d.income, d.complain, d.urge_receive, d.urge_send) = (select nvl(sum(da.receive), 0), /*总收件票数*/ nvl(sum(da.send), 0), /*总派送票数*/ nvl(sum(da.income), 0), /*总收入*/ nvl(sum(da.complain), 0), /*总投诉数*/ nvl(sum(da.urge_receive), 0), /*总催收票数*/ nvl(sum(da.urge_send), 0) /*总催派票数*/ from t_evs_courier_data da join v_emp_courier vec on da.courier_code = vec.empcode where vec.deptcode in (select c.org_code from dual union select de.deptcode from t_org_department de where de.active = 'Y' start with de.parent_org_code = c.org_code connect by prior de.deptcode = de.parent_org_code) and da.product_date = v_product_date) where d.id = c.id; end loop; /*(4.4) 根据年月,日,组织编码 统计 人均收件票数,人均派送票数,人均收入,人均投诉数,人均催收票数,人均催派票数,人均效率 */ update t_evs_orgdata_day d set d.avg_receive = round(d.receive / d.courier_num, 2), d.avg_send = round(d.send / d.courier_num, 2), d.avg_income = round(d.income / d.courier_num, 2), d.avg_complain = round(d.complain / d.courier_num, 2), d.avg_urge_receive = round(d.urge_receive / d.courier_num, 2), d.avg_urge_send = round(d.urge_send / d.courier_num, 2), d.avg_efficiency = round((d.receive + d.send) / d.courier_num, 2) where d.product_date = v_product_date and d.org_type = '3' and d.courier_num > 0; /*快递员人数>0才更新,否则不更新*/ /*(4.5)根据年月,日,组织编码 统计 人均收件票数,人均派送票数,人均收入,人均投诉数,人均催收票数,人均催派票数,人均效率 人数为0,全部更新0 */ update t_evs_orgdata_day d set d.avg_receive = 0, d.avg_send = 0, d.avg_income = 0, d.avg_complain = 0, d.avg_urge_receive = 0, d.avg_urge_send = 0, d.avg_efficiency = 0 where d.product_date = v_product_date /*生产日期*/ and d.org_type = '3' and (d.courier_num = 0 or d.courier_num is null); /*快递员人数=0才更新,全部更新为0*/ /*(5)统计 点部(营业部) 累积到当月当日当日 数据*/ /*(5.1)把 点部(营业部) 数据插入到 t_evs_orgdata_month 表 */ insert into t_evs_orgdata_month (id, product_date, org_type, org_code, org_name, parant_code, create_date) (select sys_guid() as id, v_product_date as product_date, '3' as org_type, t.org_code as org_code, t.org_name as org_name, t.parant_code as parant_code, sysdate from (select distinct d.deptcode as org_code, d.deptname as org_name, d.parent_org_code as parant_code from t_org_department d right join t_bas_express_smallzone sm on d.deptcode = sm.management where d.active = 'Y' and sm.active = 'Y') t); /*(5.2)根据 年月,日,组织编码 统计 组织表当日快递员人数 */ for c in (select d.id, d.org_code from t_evs_orgdata_month d where d.product_date = v_product_date and d.org_type = '3') loop update t_evs_orgdata_month d set d.courier_num = (select count(1) from v_emp_courier vec where vec.deptcode in (select c.org_code from dual union select de.deptcode from t_org_department de where de.active = 'Y' start with de.parent_org_code = c.org_code connect by prior de.deptcode = de.parent_org_code)) where d.id = c.id; /*组织类型为 点部(营业部) */ end loop; /*(5.3) 根据 年月,日,组织编码 统计 收件票数,派送票数,收入,投诉数,催收票数,催派票数*/ for c in (select d.id, d.org_code from t_evs_orgdata_month d where d.product_date = v_product_date and d.org_type = '3') loop update t_evs_orgdata_month d set (d.receive, d.send, d.income, d.complain, d.urge_receive, d.urge_send) = (select nvl(sum(da.receive), 0), /*总收件票数*/ nvl(sum(da.send), 0), /*总派送票数*/ nvl(sum(da.income), 0), /*总收入*/ nvl(sum(da.complain), 0), /*总投诉数*/ nvl(sum(da.urge_receive), 0), /*总催收票数*/ nvl(sum(da.urge_send), 0) /*总催派票数*/ from t_evs_courier_data da join v_emp_courier vec on da.courier_code = vec.empcode where vec.deptcode in (select c.org_code from dual union select de.deptcode from t_org_department de where de.active = 'Y' start with de.parent_org_code = c.org_code connect by prior de.deptcode = de.parent_org_code) and da.product_date between v_year_month_first and v_product_date) where d.id = c.id; end loop; /*(5.4) 根据年月,日,组织编码 统计 人均收件票数,人均派送票数,人均收入,人均投诉数,人均催收票数,人均催派票数,人均效率 */ update t_evs_orgdata_month d set d.avg_receive = round(d.receive / d.courier_num, 2), d.avg_send = round(d.send / d.courier_num, 2), d.avg_income = round(d.income / d.courier_num, 2), d.avg_complain = round(d.complain / d.courier_num, 2), d.avg_urge_receive = round(d.urge_receive / d.courier_num, 2), d.avg_urge_send = round(d.urge_send / d.courier_num, 2), d.avg_efficiency = round((d.receive + d.send) / d.courier_num, 2) where d.product_date = v_product_date and d.org_type = '3' and d.courier_num > 0; /*快递员人数>0才更新,否则不更新*/ /*(5.5)根据年月,日,组织编码 统计 人均收件票数,人均派送票数,人均收入,人均投诉数,人均催收票数,人均催派票数,人均效率 人数为0,全部更新0 */ update t_evs_orgdata_month d set d.avg_receive = 0, d.avg_send = 0, d.avg_income = 0, d.avg_complain = 0, d.avg_urge_receive = 0, d.avg_urge_send = 0, d.avg_efficiency = 0 where d.product_date = v_product_date /*生产日期*/ and d.org_type = '3' and (d.courier_num = 0 or d.courier_num is null); /*快递员人数=0才更新,全部更新为0*/ commit; exception when others then dbms_output.put_line(sqlerrm); rollback; end p_evs_day_and_month_data; / create or replace procedure p_task_manual(v_product_date in varchar2) as begin /*跑数据调用的存储过程*/ dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')); p_evs_courier_data_tongbu(v_product_date); /*1.ods同步数据*/ dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')); p_evs_courier_bigzone(v_product_date); /*2.计算快递大区的快递员人数*/ dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')); p_evs_courier_data(v_product_date); /*3.计算快递员基础数据t_evs_courier_data*/ dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')); p_evs_courier_cumulativedata(v_product_date); /*4.计算快递员当月累计数据t_evs_courier_cumulativedata*/ dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')); p_evs_courier_score(v_product_date); /*5.计算快递员积分t_evs_courier_score*/ dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')); p_evs_day_and_month_data(v_product_date); /*6.计算组织日数据t_evs_orgdata_day,组织月累计t_evs_orgdata_month*/ dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')); p_evs_courier_data_warning(v_product_date); /*7.计算预警信息*/ dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')); p_evs_courier_info(v_product_date); /*8.计算快递员所属 信息*/ dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')); end p_task_manual; / create or replace procedure p_task_scheduling(v_product_date in varchar2) as v_tab_cnt number; /** 当天 distinct表名 的个数 */ v_flag_1 number; /** 当天 enable_flag 为1的记录个数 */ begin select count(distinct l.table_name) into v_tab_cnt from t_dsjob_ods_to_evs_log l where l.insert_time >= trunc(sysdate); select count(1) into v_flag_1 from t_dsjob_ods_to_evs_log l where l.insert_time >= trunc(sysdate) and l.enable_flag = '1'; /* 如果ODS已经把5张表(T_BAS_COURIER_SCHEDULE,T_EVS_ERROR_PICKTICKET,T_EVS_WAYBILLTICKET T_BAS_EXPRESS_SMALLZONE,T_EVS_EXP_PERSONCITY)的数据跑完,distinct表名 的个数=5 , 并且存在没有执行的(即是存在enable_flag = 1)的,才执行. 把enable_flag状态改为 2 .下次执行job跑存储过程判断v_tab_cnt和v_flag_1的时候, 不再跑, */ if (v_tab_cnt = 5 and v_flag_1 > 0) then /* 把enable_flag状态改为 2*/ for c in (select rowid from t_dsjob_ods_to_evs_log l where l.insert_time >= trunc(sysdate) and l.enable_flag = '1') loop update t_dsjob_ods_to_evs_log l set l.enable_flag = '2' where l.rowid = c.rowid; end loop; commit; /*调用存储过程*/ p_task_manual(v_product_date); end if; end p_task_scheduling; / CREATE OR REPLACE PROCEDURE p_user_org_auth_proc(var_user_code IN VARCHAR2) IS BEGIN DELETE FROM t_auth_user_org_auth WHERE emp_code = var_user_code; INSERT INTO t_auth_user_org_auth (emp_code, org_code) SELECT user_code AS emp_code, org_code AS org_code FROM t_auth_userdepartment userdept WHERE userdept.user_code = var_user_code UNION SELECT dataauth.user_code AS emp_code, org.org_code AS org_code FROM (SELECT DISTINCT (org.code) AS org_code, connect_by_root(org.code) AS root_code FROM (SELECT dept.deptcode AS code, dept.parent_org_code AS parent_org_code FROM t_org_department dept WHERE dept.active = 'Y') org LEFT JOIN (SELECT DISTINCT (userdept.org_code) AS org_code FROM t_auth_userdepartment userdept WHERE userdept.include_sub_org = 'Y' AND userdept.user_code = var_user_code) dataauth ON dataauth.org_code = org.code CONNECT BY PRIOR org.code = org.parent_org_code START WITH org.code = dataauth.org_code) org, t_auth_userdepartment dataauth WHERE org.root_code = dataauth.org_code AND dataauth.include_sub_org = 'Y' AND dataauth.user_code = var_user_code; COMMIT; END p_user_org_auth_proc; / create or replace trigger trg_org_employee after insert or update or delete on t_org_employee for each row declare v_position varchar2(300); v_position_new varchar2(100); v_empcode varchar2(64); v_user_flag number; /**用户是否在 用户表 存在*/ v_userrole_flag number; /**用户是否在 用户角色表 存在*/ begin v_position := '点部经理,营业部经理,营业区区域经理,区部高级经理,大区总经理,快递大区总经理,事业部总裁'; v_position_new := :new.position; v_empcode := :new.empcode; select count(1) into v_user_flag from t_auth_user u where u.emp_code = v_empcode; select count(1) into v_userrole_flag from t_auth_userrole r where r.user_code = v_empcode and r.role_code = 'dpap'; /**如果新员工职位 不在 以上几个职位当中,直接退出触发器*/ if instr(v_position, v_position_new) <= 0 then return; end if; /**如果新员工职位 在 以上几个职位当中*/ case /**(1)新增员工*/ when inserting then /**(1.1)新增用户*/ insert into t_auth_user values (v_empcode, v_empcode, '4QrcOUm6Wau+VuBX8g+IPg=1', null, '000000', sysdate, '000000', sysdate, sysdate, sysdate + 300000, 'Y', sys_guid()); /**(1.2)新增用户角色*/ insert into t_auth_userrole values (sys_guid(), 'dpap', v_empcode); /**(2)修改员工*/ when updating then /**(2.1员工离职)*/ if :new.status = 0 then /**(2.1.1)删除用户*/ delete from t_auth_user u where u.emp_code = v_empcode; /**(2.1.2)删除用户角色*/ delete from t_auth_userrole r where r.user_code = v_empcode; /**(2.2员工岗位变动,并且岗位变动为 v_position 中一种)*/ else /**(2.2.1 用户不存在 ,添加用户)*/ if v_user_flag <= 0 then insert into t_auth_user values (v_empcode, v_empcode, '4QrcOUm6Wau+VuBX8g+IPg=1', null, '000000', sysdate, '000000', sysdate, sysdate, sysdate + 300000, 'Y', sys_guid()); end if; /**(2.2.2 用户角色不存在 ,添加用户角色)*/ if v_user_flag <= 0 then insert into t_auth_userrole values (sys_guid(), 'dpap', v_empcode); end if; end if; /**(3)删除员工*/ when deleting then /**(3.1)删除用户*/ delete from t_auth_user u where u.emp_code = :old.empcode; /**(3.2)删除用户角色*/ delete from t_auth_userrole r where r.user_code = :old.empcode; end case; end;
oracle存储过程
最新推荐文章于 2021-07-06 14:36:28 发布
扫一扫
297
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
