from pubpeople@HR_51_PROD.REGRESS.RDBMS.DEV.US.ORACLE.COM pp
where length(pp.user_id)=4 and not exists(select 1 from eip_sys_user where username = 'g'||pp.user_id);
--更新职位信息
begin
update XT_ADDRESS_LIST_GLOB t set t.adminpost=(select adminpost from pubpeople@hr_51_prod.regress.rdbms.dev.us.oracle.com pp, eip_sys_user u where t.account=u.id and u.username = 'g'||pp.user_id);
exception
when others then
dbms_output.put_line('更新职位信息异常');
end;
--<<<<<<<<<<<<<<<天联用户>>>>>>>>>>>>
begin
--天联人员 删除
delete from eip_sys_user t
where username like 'g____%'
and not exists(
select 1 from v_lz_user pp
where pp.UG_UserGrpName like '天联公司' and 'g'||pp.SU_UserCode=t.username) and HRUSER='n';
--天联人员 修改
update eip_sys_user t
set realname = (select distinct(SU_UserName) from v_lz_user where 'g'||SU_UserCode=t.username),
deptid = (select distinct(d.id) from v_lz_user u,eip_sys_department d where u.UG_UserGrpName like '天联公司' and 'g'||u.SU_UserCode=t.username and d.section =u.UG_UserGrpName and d.dept=u.room)
where username like 'g____%' and HRUSER='n';
--天联人员 增加比EIP中多的人员
insert into eip_sys_user
select to_char(EIP_USERID.nextval),'g'||u.SU_UserCode, u.SU_UserName, d.id,'n' from v_lz_user u,eip_sys_department d
where u.UG_UserGrpName like '天联公司' and d.section =u.UG_UserGrpName and d.dept=u.room and not exists(select 1 from eip_sys_user where username = 'g'||u.SU_UserCode);
exception
when others then
dbms_output.put_line('天联公司人员同步异常');
end;
--<<<<<<<<<<<<<<<天联用户>>>>>>>>>>>>
--删除没有部门的人员
delete eip_sys_user t where t.deptid is null;
insert into eip_sys_user values( '-1','-1','系统提醒','','sg');