--start------注意----------------------------------------------------------------------------------------------------------------------------------------------
-- 1)前提必须先初始化网点表数据,网点数据每天凌晨0:00点从旧CMS网点表(tb_product_agency)抽到网点表(t_network)
-- 2)经销商渠道关系同步表(t_dealer_channel_rel_sync)和渠道负责人关系同步表(t_channel_charge_rel_sync)数据来源是信息中心提供的"渠道_生产商_品牌_负责人关系表.xls"
-- 3)经销商用户同步表(t_dealer_user_sync)和经销商开发员同步表(t_dealer_developer_sync)数据来源是内部库dbo.JXX_Dealer_Info表,使用ETL工个抽数
-- 4)cas.sys_user表tel和email字段长度都修改为200
--end--------注意----------------------------------------------------------------------------------------------------------------------------------------------
--[1]----satrt--------根据经销商编码,把网点表的城市、省份等字段同步到经销商表--------
merge into t_dealer a
using t_network b
on(a.DEALER_CODE=b.DEALER_CODE)
when matched then
update set a.province_id = b.province_id
,a.city_id = b.city_id
,a.county_id = b.county_id
,a.manf_id = b.manf_id
,a.brand_id = b.brand_id
,a.shop_name = b.dealer_full_name
,a.address = b.dealer_full_addr
,a.sale_tel = b.sale_tel
,a.dealer_nature_val = b.dealer_nature_val;
--[1]----end--------根据经销商编码,把网点表的城市、省份等字段同步到经销商表--------
--[2]----satrt--------根据经销商编码,创建经销商渠道关系--------
/*--经销商渠道关系同步表
create table t_dealer_channel_rel_sync (
dealer_code varchar2(300),
channel_id number
)*/
merge into t_dealer a
using t_dealer_channel_rel_sync b
on(a.dealer_code = b.dealer_code)
when matched then
update set a.channel_id = b.channel_id;
--把没有"经销商渠道关系"的渠道ID都置为-1
--update t_dealer t set t.channel_id = -1 where t.channel_id is null;
--[2]----end--------根据经销商编码,创建经销商渠道关系--------
--[3]----satrt--------初始化渠道负责人关系(价格/终端、商务政策)--------
/*--渠道负责人关系同步表
create table t_channel_charge_rel_sync(
CHANNEL_ID number,
PRICE number,
TERMINAL number,
BUSINESS number,
COLLECTOR_NAME varchar2(100),
ASSESSOR_NAME varchar2(100),
BUSINESS_NAME varchar2(100),
COLLECTOR_ID varchar2(32),
ASSESSOR_ID varchar2(32),
BUSINESS_ID varchar2(32)
)*/
--同步采集员ID(同步临时表)
merge into t_channel_charge_rel_sync a
using cas.sys_user b
on (a.COLLECTOR_NAME = b.user_name )
when matched then
update set a.COLLECTOR_ID = b.user_id;
--同步审核员ID(同步临时表)
merge into t_channel_charge_rel_sync a
using cas.sys_user b
on (a.ASSESSOR_NAME = b.user_name)
when matched then
update set a.ASSESSOR_ID = b.user_id;
--同步商务政策负责人ID(同步临时表)
merge into t_channel_charge_rel_sync a
using cas.sys_user b
on (a.BUSINESS_NAME = b.user_name)
when matched then
update set a.BUSINESS_ID = b.user_id;
--初始化渠道负责人关系(价格/终端)
insert into t_channel_charge_rel
select sys_guid()
,t.channel_id
,1 as PRODUCT_TYPE_VAL
,t.collector_id
,t.assessor_id
,''
,sysdate
,''
,sysdate
from t_channel_charge_rel_sync t
where t.price = 1 or t.terminal = 1
--初始化渠道负责人关系(商务政策)
insert into t_channel_charge_rel
select sys_guid()
,t.channel_id
,2 as PRODUCT_TYPE_VAL
,t.business_id
,t.business_id
,''
,sysdate
,''
,sysdate
from t_channel_charge_rel_sync t
where t.business = 1;
select * from t_channel_charge_rel_main;
truncate table t_channel_charge_rel_main
insert into t_channel_charge_rel_main select * from t_channel_charge_rel
--[3]----end--------初始化渠道负责人关系(价格/终端、商务政策)--------
--[4]----start--------根据经销商编码,把网点表的营运状态、集团ID字段同步到经销商背景信息表--------
merge into t_dealer_background a
using t_network b
on(a.DEALER_CODE=b.DEALER_CODE)
when matched then
update set a.group_id = b.group_id
,a.operation_state_val = b.operation_state_val;
--处理营运状态不为数字的都为20
--update t_dealer_background b set b.operation_state_val = 20 where b.operation_state_val != '20'
--[4]----end--------根据经销商编码,把网点表的营运状态、集团ID字段同步到经销商背景信息表--------
--[5]----start--------根据码表(t_code_table)更新"删除详细原因"字段,初始化时此字段为中文--------
update t_dealer d
set d.del_detail_reason = (select a.code_val
from t_code_table a
where a.p_code =
'PARAM_DEALER_DEL_DETAIL_REASON'
and d.del_detail_reason = a.code_name)
--更新后,"删除详细原因"字段还是空的数据都置为6(其他)
update t_dealer d
set d.del_detail_reason = 6
where d.del_detail_reason is null;
--[5]----end--------根据码表(t_code_table)更新"删除详细原因"字段,初始化时此字段为中文--------
--[6]----start--------根据码表(t_code_table)更新"银行"字段,初始化时此字段为中文--------
update t_dealer_account d
set d.bank_val = (select a.code_val
from t_code_table a
where a.p_code =
'PARAM_BANK'
and d.bank_val = a.code_name)
--更新后,"银行"字段还是空的数据都置为44(其他)
update t_dealer_account d
set d.bank_val = 44
where d.bank_val is null;
--[6]----end--------根据码表(t_code_table)更新"银行"字段,初始化时此字段为中文--------
--[7]----start--------添加经销商用户并分配角色--------
/*--创建经销商用户同步临时表
create table t_dealer_user_sync(
dealer_user_id varchar2(32),
dealer_user_name varchar2(100),
GENDER number,
TEL varchar2(100),
EMAIL varchar2(100)
)*/
/*
--创建md5函数(用于密码加密)
CREATE OR REPLACE FUNCTION MD5(passwd IN VARCHAR2) RETURN VARCHAR2 IS retval varchar2(32);
BEGIN
retval := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => passwd)) ;
RETURN retval;
END;
--测试md5函数
select md5(123456) from dual;
--随机6位小数
select substr(abs(dbms_random.random),1,6) from dual;
*/
select * from t_dealer_user_sync;
-- 1)删除原来的经销商用户
delete from cas.sys_user c where c.unit_id = '728eac62b54f4f4aa3aaacff70fbf97c';
-- 2)使用ETL工具从内部库抽数到t_dealer_user_sync
-- 3)t_dealer_user_sync表添加uuid主键
update t_dealer_user_sync t set t.dealer_user_id = sys_guid()
-- 4)往cas.sys_user表里插入经销商用户数据
insert into cas.sys_user
select t.dealer_user_id as USER_ID
,substr(abs(dbms_random.random),1,6) as LOGIN_ID
,md5(123456) as PASSWORD
,t.dealer_user_name as USER_NAME
,t.gender
,t.tel
--,''
,''
,sysdate
,add_months(sysdate,240)
,''
,''
,sysdate
,''
,sysdate
,1
,1
,''
,t.email
-- ,''
,'728eac62b54f4f4aa3aaacff70fbf97c'
,0
,t.dealer_user_name
from t_dealer_user_sync t;
/* select * from cas.sys_user u where u.unit_id = '728eac62b54f4f4aa3aaacff70fbf97c'
delete from cas.sys_user u where u.unit_id = '728eac62b54f4f4aa3aaacff70fbf97c'
--truncate table t_dealer_user_sync;
select * from t_dealer_user_sync t where t.dealer_user_name = '薛天宇'*/
-- 5)往cas.sys_user_role表里插入经销商用户角色数据
insert into cas.sys_user_role
select sys_guid()
,t.dealer_user_id
,'b2371031e6af42b3b6bf155336d50e00'
,''
from t_dealer_user_sync t
-- 6)往更新t_dealer表“联系人ID”
update t_dealer a
set a.CONTACTS_ID = (select b.DEALER_USER_ID
from t_dealer_user_sync b
where b.dealer_user_name = a.CONTACTS_ID)
/* select * from t_dealer_user_sync;
select * from t_dealer;*/
--[7]----end--------添加经销商用户并分配角色--------
--[8]----start--------添加经销商开发员并分配角色、更新经销商背景表"开发员ID"--------
-- 1)单独为"管理员"添加用户数据 OA_58是收集部
insert into cas.sys_user
select sys_guid() as USER_ID
,'admin' as LOGIN_ID
,md5(123456) as PASSWORD
,'管理员' as USER_NAME
,1
,t.tel
,''
,sysdate
,add_months(sysdate,240)
,''
,''
,sysdate
,''
,sysdate
,1
,1
,''
,t.email
,'OA_58'
,0
,'管理员'
from dual;
-- 2)查询"管理员"用户ID
select * from cas.sys_user u where u.user_name = '管理员'
-- 3)单独为"管理员"添加用户角色数据
insert into cas.sys_user_role
select sys_guid()
,'0EB90118D51647CDE050A8C01B018B69'
,'3e34f0c3acd24e32ba91808896efa0c4'
,''
from t_dealer_user_sync t
/* --3e34f0c3acd24e32ba91808896efa0c4 开发员角色ID
select * from cas.sys_user_role r where r.role_id = '3e34f0c3acd24e32ba91808896efa0c4';
--创建经销商开发员同步临时表
create table t_dealer_developer_sync(
dealer_developer_id varchar2(32),
dealer_developer_name varchar2(100),
inner_developer_id varchar2(32)
)
*/
-- 4)根据中文名称查询用户ID
merge into t_dealer_developer_sync a
using cas.sys_user b
on (a.DEALER_DEVELOPER_NAME = b.user_name)
when matched then
update set a.DEALER_DEVELOPER_ID = b.user_id;
-- 5)删除用户ID为空的(即已离职)
delete from t_dealer_developer_sync t where t.dealer_developer_id is null;
-- 6)往cas.sys_user_role表里插入经销商开发员角色数据
insert into cas.sys_user_role
select sys_guid()
,t.dealer_developer_id
,'3e34f0c3acd24e32ba91808896efa0c4'
,''
from t_dealer_developer_sync t
-- 7)更新经销商背景表"开发员ID"
update t_dealer_background a
set a.developer_id = (select b.dealer_developer_id
from t_dealer_developer_sync b
where b.inner_developer_id = a.developer_id)
select * from t_dealer_developer_sync;
--[8]----end--------添加经销商开发员并分配角色、更新经销商背景表"开发员ID"--------
--[9]----start--------分配采集员角色--------
insert into cas.sys_user_role
select sys_guid()
,t.collector_id
,(select t.role_id from cas.sys_role t where t.role_name = '收集员' and t.role_code = 'ROLE_DEALER_COLLECTOR')
,''
from (
select distinct t.collector_id
from t_channel_charge_rel t
where t.collector_id is not null
) t
select * from cas.sys_role;
--[9]----start--------分配审核员角色--------
insert into cas.sys_user_role
select sys_guid()
,t.ASSESSOR_ID
,(select t.role_id from cas.sys_role t where t.role_name = '审核员' and t.role_code = 'ROLE_DEALER_ASSESSOR')
,''
from (
select distinct t.ASSESSOR_ID
from t_channel_charge_rel t
where t.ASSESSOR_ID is not null
) t
select * from cas.sys_role;
--[9]----end--------分配审核员角色--------
---------------------------------------------------------------------------------------------------------------
------------------------------------定时任务---------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
/* --删除JOB
begin
dbms_job.remove(54);
commit;
end;
--查看job信息
select * from user_jobs ;
select * from all_jobs ;*/
----start--------经销商信息JOB(开发中)----------
--每天凌晨3:00
declare
job number;
begin
--sys.dbms_job.submit(job, 'trace_table_bat;', sysdate, 'sysdate+1/1440'); --每分钟
sys.dbms_job.submit(job, 'proc_dealer_auto_sync;', sysdate, 'TRUNC(sysdate)+1+3/24');
commit;
end;
----end--------经销商信息JOB(开发中)------------------------
----start--------旧CMS网点表同步到经销商网点表----------
--每天0:00
declare
job number;
begin
sys.dbms_job.submit(job, 'proc_network_auto_sync;', sysdate, 'TRUNC(sysdate)+1');
commit;
end;
----end--------旧CMS网点表同步到经销商网点表------------------------
----start--------跟踪表JOB------------------------
--每月10号00:00点
declare
job number;
begin
--sys.dbms_job.submit(job, 'trace_table_bat;', sysdate, 'sysdate+1/1440'); --每分钟
sys.dbms_job.submit(job, 'proc_trace_table_bat;', sysdate, 'TRUNC(LAST_DAY(SYSDATE)+10)');
commit;
end;
--每月24号00:00点
declare
job number;
begin
--sys.dbms_job.submit(job, 'trace_table_bat;', sysdate, 'sysdate+1/1440'); --每分钟
sys.dbms_job.submit(job, 'proc_trace_table_bat;', sysdate, 'TRUNC(LAST_DAY(SYSDATE)+24)');
commit;
end;
----end--------跟踪表JOB------------------------
----start--------年度开发计划城市表JOB----------
--每年12月31号23:00点
declare
job number;
begin
--sys.dbms_job.submit(job, 'proc_year_city_auto_bat;', sysdate, 'sysdate+1/1440'); --每分钟
--sys.dbms_job.submit(job, 'proc_year_city_auto_bat;', sysdate, 'add_months(trunc(sysdate,'yyyy'),11) + 30 +(23*60)/(24*60)');
commit;
end;
----end--------年度开发计划城市表JOB------------------------
-- 1)前提必须先初始化网点表数据,网点数据每天凌晨0:00点从旧CMS网点表(tb_product_agency)抽到网点表(t_network)
-- 2)经销商渠道关系同步表(t_dealer_channel_rel_sync)和渠道负责人关系同步表(t_channel_charge_rel_sync)数据来源是信息中心提供的"渠道_生产商_品牌_负责人关系表.xls"
-- 3)经销商用户同步表(t_dealer_user_sync)和经销商开发员同步表(t_dealer_developer_sync)数据来源是内部库dbo.JXX_Dealer_Info表,使用ETL工个抽数
-- 4)cas.sys_user表tel和email字段长度都修改为200
--end--------注意----------------------------------------------------------------------------------------------------------------------------------------------
--[1]----satrt--------根据经销商编码,把网点表的城市、省份等字段同步到经销商表--------
merge into t_dealer a
using t_network b
on(a.DEALER_CODE=b.DEALER_CODE)
when matched then
update set a.province_id = b.province_id
,a.city_id = b.city_id
,a.county_id = b.county_id
,a.manf_id = b.manf_id
,a.brand_id = b.brand_id
,a.shop_name = b.dealer_full_name
,a.address = b.dealer_full_addr
,a.sale_tel = b.sale_tel
,a.dealer_nature_val = b.dealer_nature_val;
--[1]----end--------根据经销商编码,把网点表的城市、省份等字段同步到经销商表--------
--[2]----satrt--------根据经销商编码,创建经销商渠道关系--------
/*--经销商渠道关系同步表
create table t_dealer_channel_rel_sync (
dealer_code varchar2(300),
channel_id number
)*/
merge into t_dealer a
using t_dealer_channel_rel_sync b
on(a.dealer_code = b.dealer_code)
when matched then
update set a.channel_id = b.channel_id;
--把没有"经销商渠道关系"的渠道ID都置为-1
--update t_dealer t set t.channel_id = -1 where t.channel_id is null;
--[2]----end--------根据经销商编码,创建经销商渠道关系--------
--[3]----satrt--------初始化渠道负责人关系(价格/终端、商务政策)--------
/*--渠道负责人关系同步表
create table t_channel_charge_rel_sync(
CHANNEL_ID number,
PRICE number,
TERMINAL number,
BUSINESS number,
COLLECTOR_NAME varchar2(100),
ASSESSOR_NAME varchar2(100),
BUSINESS_NAME varchar2(100),
COLLECTOR_ID varchar2(32),
ASSESSOR_ID varchar2(32),
BUSINESS_ID varchar2(32)
)*/
--同步采集员ID(同步临时表)
merge into t_channel_charge_rel_sync a
using cas.sys_user b
on (a.COLLECTOR_NAME = b.user_name )
when matched then
update set a.COLLECTOR_ID = b.user_id;
--同步审核员ID(同步临时表)
merge into t_channel_charge_rel_sync a
using cas.sys_user b
on (a.ASSESSOR_NAME = b.user_name)
when matched then
update set a.ASSESSOR_ID = b.user_id;
--同步商务政策负责人ID(同步临时表)
merge into t_channel_charge_rel_sync a
using cas.sys_user b
on (a.BUSINESS_NAME = b.user_name)
when matched then
update set a.BUSINESS_ID = b.user_id;
--初始化渠道负责人关系(价格/终端)
insert into t_channel_charge_rel
select sys_guid()
,t.channel_id
,1 as PRODUCT_TYPE_VAL
,t.collector_id
,t.assessor_id
,''
,sysdate
,''
,sysdate
from t_channel_charge_rel_sync t
where t.price = 1 or t.terminal = 1
--初始化渠道负责人关系(商务政策)
insert into t_channel_charge_rel
select sys_guid()
,t.channel_id
,2 as PRODUCT_TYPE_VAL
,t.business_id
,t.business_id
,''
,sysdate
,''
,sysdate
from t_channel_charge_rel_sync t
where t.business = 1;
select * from t_channel_charge_rel_main;
truncate table t_channel_charge_rel_main
insert into t_channel_charge_rel_main select * from t_channel_charge_rel
--[3]----end--------初始化渠道负责人关系(价格/终端、商务政策)--------
--[4]----start--------根据经销商编码,把网点表的营运状态、集团ID字段同步到经销商背景信息表--------
merge into t_dealer_background a
using t_network b
on(a.DEALER_CODE=b.DEALER_CODE)
when matched then
update set a.group_id = b.group_id
,a.operation_state_val = b.operation_state_val;
--处理营运状态不为数字的都为20
--update t_dealer_background b set b.operation_state_val = 20 where b.operation_state_val != '20'
--[4]----end--------根据经销商编码,把网点表的营运状态、集团ID字段同步到经销商背景信息表--------
--[5]----start--------根据码表(t_code_table)更新"删除详细原因"字段,初始化时此字段为中文--------
update t_dealer d
set d.del_detail_reason = (select a.code_val
from t_code_table a
where a.p_code =
'PARAM_DEALER_DEL_DETAIL_REASON'
and d.del_detail_reason = a.code_name)
--更新后,"删除详细原因"字段还是空的数据都置为6(其他)
update t_dealer d
set d.del_detail_reason = 6
where d.del_detail_reason is null;
--[5]----end--------根据码表(t_code_table)更新"删除详细原因"字段,初始化时此字段为中文--------
--[6]----start--------根据码表(t_code_table)更新"银行"字段,初始化时此字段为中文--------
update t_dealer_account d
set d.bank_val = (select a.code_val
from t_code_table a
where a.p_code =
'PARAM_BANK'
and d.bank_val = a.code_name)
--更新后,"银行"字段还是空的数据都置为44(其他)
update t_dealer_account d
set d.bank_val = 44
where d.bank_val is null;
--[6]----end--------根据码表(t_code_table)更新"银行"字段,初始化时此字段为中文--------
--[7]----start--------添加经销商用户并分配角色--------
/*--创建经销商用户同步临时表
create table t_dealer_user_sync(
dealer_user_id varchar2(32),
dealer_user_name varchar2(100),
GENDER number,
TEL varchar2(100),
EMAIL varchar2(100)
)*/
/*
--创建md5函数(用于密码加密)
CREATE OR REPLACE FUNCTION MD5(passwd IN VARCHAR2) RETURN VARCHAR2 IS retval varchar2(32);
BEGIN
retval := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => passwd)) ;
RETURN retval;
END;
--测试md5函数
select md5(123456) from dual;
--随机6位小数
select substr(abs(dbms_random.random),1,6) from dual;
*/
select * from t_dealer_user_sync;
-- 1)删除原来的经销商用户
delete from cas.sys_user c where c.unit_id = '728eac62b54f4f4aa3aaacff70fbf97c';
-- 2)使用ETL工具从内部库抽数到t_dealer_user_sync
-- 3)t_dealer_user_sync表添加uuid主键
update t_dealer_user_sync t set t.dealer_user_id = sys_guid()
-- 4)往cas.sys_user表里插入经销商用户数据
insert into cas.sys_user
select t.dealer_user_id as USER_ID
,substr(abs(dbms_random.random),1,6) as LOGIN_ID
,md5(123456) as PASSWORD
,t.dealer_user_name as USER_NAME
,t.gender
,t.tel
--,''
,''
,sysdate
,add_months(sysdate,240)
,''
,''
,sysdate
,''
,sysdate
,1
,1
,''
,t.email
-- ,''
,'728eac62b54f4f4aa3aaacff70fbf97c'
,0
,t.dealer_user_name
from t_dealer_user_sync t;
/* select * from cas.sys_user u where u.unit_id = '728eac62b54f4f4aa3aaacff70fbf97c'
delete from cas.sys_user u where u.unit_id = '728eac62b54f4f4aa3aaacff70fbf97c'
--truncate table t_dealer_user_sync;
select * from t_dealer_user_sync t where t.dealer_user_name = '薛天宇'*/
-- 5)往cas.sys_user_role表里插入经销商用户角色数据
insert into cas.sys_user_role
select sys_guid()
,t.dealer_user_id
,'b2371031e6af42b3b6bf155336d50e00'
,''
from t_dealer_user_sync t
-- 6)往更新t_dealer表“联系人ID”
update t_dealer a
set a.CONTACTS_ID = (select b.DEALER_USER_ID
from t_dealer_user_sync b
where b.dealer_user_name = a.CONTACTS_ID)
/* select * from t_dealer_user_sync;
select * from t_dealer;*/
--[7]----end--------添加经销商用户并分配角色--------
--[8]----start--------添加经销商开发员并分配角色、更新经销商背景表"开发员ID"--------
-- 1)单独为"管理员"添加用户数据 OA_58是收集部
insert into cas.sys_user
select sys_guid() as USER_ID
,'admin' as LOGIN_ID
,md5(123456) as PASSWORD
,'管理员' as USER_NAME
,1
,t.tel
,''
,sysdate
,add_months(sysdate,240)
,''
,''
,sysdate
,''
,sysdate
,1
,1
,''
,t.email
,'OA_58'
,0
,'管理员'
from dual;
-- 2)查询"管理员"用户ID
select * from cas.sys_user u where u.user_name = '管理员'
-- 3)单独为"管理员"添加用户角色数据
insert into cas.sys_user_role
select sys_guid()
,'0EB90118D51647CDE050A8C01B018B69'
,'3e34f0c3acd24e32ba91808896efa0c4'
,''
from t_dealer_user_sync t
/* --3e34f0c3acd24e32ba91808896efa0c4 开发员角色ID
select * from cas.sys_user_role r where r.role_id = '3e34f0c3acd24e32ba91808896efa0c4';
--创建经销商开发员同步临时表
create table t_dealer_developer_sync(
dealer_developer_id varchar2(32),
dealer_developer_name varchar2(100),
inner_developer_id varchar2(32)
)
*/
-- 4)根据中文名称查询用户ID
merge into t_dealer_developer_sync a
using cas.sys_user b
on (a.DEALER_DEVELOPER_NAME = b.user_name)
when matched then
update set a.DEALER_DEVELOPER_ID = b.user_id;
-- 5)删除用户ID为空的(即已离职)
delete from t_dealer_developer_sync t where t.dealer_developer_id is null;
-- 6)往cas.sys_user_role表里插入经销商开发员角色数据
insert into cas.sys_user_role
select sys_guid()
,t.dealer_developer_id
,'3e34f0c3acd24e32ba91808896efa0c4'
,''
from t_dealer_developer_sync t
-- 7)更新经销商背景表"开发员ID"
update t_dealer_background a
set a.developer_id = (select b.dealer_developer_id
from t_dealer_developer_sync b
where b.inner_developer_id = a.developer_id)
select * from t_dealer_developer_sync;
--[8]----end--------添加经销商开发员并分配角色、更新经销商背景表"开发员ID"--------
--[9]----start--------分配采集员角色--------
insert into cas.sys_user_role
select sys_guid()
,t.collector_id
,(select t.role_id from cas.sys_role t where t.role_name = '收集员' and t.role_code = 'ROLE_DEALER_COLLECTOR')
,''
from (
select distinct t.collector_id
from t_channel_charge_rel t
where t.collector_id is not null
) t
select * from cas.sys_role;
--[9]----start--------分配审核员角色--------
insert into cas.sys_user_role
select sys_guid()
,t.ASSESSOR_ID
,(select t.role_id from cas.sys_role t where t.role_name = '审核员' and t.role_code = 'ROLE_DEALER_ASSESSOR')
,''
from (
select distinct t.ASSESSOR_ID
from t_channel_charge_rel t
where t.ASSESSOR_ID is not null
) t
select * from cas.sys_role;
--[9]----end--------分配审核员角色--------
---------------------------------------------------------------------------------------------------------------
------------------------------------定时任务---------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
/* --删除JOB
begin
dbms_job.remove(54);
commit;
end;
--查看job信息
select * from user_jobs ;
select * from all_jobs ;*/
----start--------经销商信息JOB(开发中)----------
--每天凌晨3:00
declare
job number;
begin
--sys.dbms_job.submit(job, 'trace_table_bat;', sysdate, 'sysdate+1/1440'); --每分钟
sys.dbms_job.submit(job, 'proc_dealer_auto_sync;', sysdate, 'TRUNC(sysdate)+1+3/24');
commit;
end;
----end--------经销商信息JOB(开发中)------------------------
----start--------旧CMS网点表同步到经销商网点表----------
--每天0:00
declare
job number;
begin
sys.dbms_job.submit(job, 'proc_network_auto_sync;', sysdate, 'TRUNC(sysdate)+1');
commit;
end;
----end--------旧CMS网点表同步到经销商网点表------------------------
----start--------跟踪表JOB------------------------
--每月10号00:00点
declare
job number;
begin
--sys.dbms_job.submit(job, 'trace_table_bat;', sysdate, 'sysdate+1/1440'); --每分钟
sys.dbms_job.submit(job, 'proc_trace_table_bat;', sysdate, 'TRUNC(LAST_DAY(SYSDATE)+10)');
commit;
end;
--每月24号00:00点
declare
job number;
begin
--sys.dbms_job.submit(job, 'trace_table_bat;', sysdate, 'sysdate+1/1440'); --每分钟
sys.dbms_job.submit(job, 'proc_trace_table_bat;', sysdate, 'TRUNC(LAST_DAY(SYSDATE)+24)');
commit;
end;
----end--------跟踪表JOB------------------------
----start--------年度开发计划城市表JOB----------
--每年12月31号23:00点
declare
job number;
begin
--sys.dbms_job.submit(job, 'proc_year_city_auto_bat;', sysdate, 'sysdate+1/1440'); --每分钟
--sys.dbms_job.submit(job, 'proc_year_city_auto_bat;', sysdate, 'add_months(trunc(sysdate,'yyyy'),11) + 30 +(23*60)/(24*60)');
commit;
end;
----end--------年度开发计划城市表JOB------------------------