HR同步功能说明

1、1、需要根据需要先写好外部系统数据库视图，并在外部系统数据库中创建好对应的视图(1)分部视图createview v_oa_com as select b. ID , b. Name, b. Code , a. ParentID , b. CreateTime , b. StatusID from ORG_co mpanya , org_unitb where a. UnitID =b. ID(2)部门视图createview v_oa_deptment asselect b. ID , b. Name, b. Code , e. partmentid , a. CompanyID , b.

2、CreateTime , b. Stat usIDfrom org_departmenta , org_unitb ,( select c. idpartmentid , d. idid from org_uni tc , org_unitdwhere c. Code =substring ( d. code , 1, LEN( d. code )- CHARINDEX ( ., reverse ( d. code ) e where a . UnitID =b . ID and b. ID =e. ID(3)岗位视图createview v_oa_jobtitle asselect ID ,

3、 Name, code , unitid , GETDATE() datatime from org_position(4)人员视图createview v_oa_hrmresource asselect 10aqjb , casewhen e. otherunit !=NULL then a. code else end loginid , b. PositionID jobid , c. UnitIDdepid , d. Type_ status , a. EM_ID , a. Name, a. Sex , a. Birthday , a. C, a . IDCardID , a . Te

4、lphone , a . EMail , a. FItemID , a .ode , a. Mobile , a. NativePlace FParentID , a. DimissionTime,1 loaction , 7 yuyan from hm_employeesa leftouterjoinv_oa_hrmjobbon a. EM_ID =b. EM_IDleftouterjoinv_oa_jobtitlec on b . PositionID =c . IDleftouterjoin hm_em_workinfod on a. EM_ID =d. EM_ID leftouterj

5、oin hm_employeesaddinfoe on a . EM_ID =e. EM_ID2、在OA系统中配置外部系统的数据源 配置数据源。3、进彳T HR同步的配置工作HRR步fit MR 亦自免。向胸胞mciTE即盾至和c手工局田小血局肆局禁戒室班V4 日D2JJCSV好1mriEFraw力哥v- G卓小却犯a conniitrti w#旧步曲.修蕾ma jTrrrKLT*注意：增量标识字段一定是时间字段。分部同步设置:窃E小苒MMEASIM岬0* F,.由情吩的*/丽小点诩蚁砒51 对rn*cpww矢嶙色gs上#卧场韭EE百K*mQidgMHifWi(y白洞p-iiettid| |削

6、Q*t度 udd部门同步设置:酬：i裳词曲MttE】F f-LL-身工下位W或不吟&筠，占限F田S丝加，黑 KlI哙应上乘热除0季岫MMXR六加Cl；dCl.StsQnsiGiQpiHifrirfld所需滤sip6f 讨1即mrritb工行录用glea白*e.h讣spt i_rcr&ak咤=1* t- l转换规则:select case when ?currentvalue=1 then 0 when ?currentvalue=2 then 1 else 1 end statusid岗位同步:廿都田关室区日当门居小题智 写煌3E而前iiFE衣同零gE 用也心尚生和沃星注可中生号巫裾HIW!M

7、货主晚Id能怆侬人员同步:片钢:E4异父/于 甲学属g身忖信步般卬之需目臂.二百n吊花看二冷 吊营青Fmt面亍搐、M固!上由！加l帮京启歌冏位1HI苧34-m_dmt|ridm-e岬Q邺2Q白航声K央虻咽1一变为：.qhC4由VSstfftoui军Mq号qQiMlMi,w同心曼息-noth tpl 2口1s-44、同步测试过程(1)如果同步之后需要再次测试同步需要先删除数据库表中数据，然后再修改同步时间点, 具体对应表如下：select * from HrmCompany-查询公司select* from HrmSubCompany-查询分部select* from HrmDepartment

8、 - 查询音select * from HrmJobTitles- 查询岗位select * from HrmResource-查询人员 修改 hrm_syntsupda.t= hrmi synts set sn3=l 197Q01-01 : : 00 1 where id =4d=let= f roan HcmDe p axtafte nt一I箱果|_消息I山加NT业:11 . :ilescstr2 2O1v-D704同声分部 同少部门 同步国精 同:F人员where id =4b.CreateTime,update hrm_synts set synts =1970-01-01 00:00

9、:00如果是重新测试同步分部，则 ID=1,以此类推5、数据库系统中创建视图的语句为 创建分部：createview v_oa_com as SELECT b.ID, b.Name, b.Code, a.ParentID, b.CreateTime, b.StatusID FROM dbo.ORG_Company AS a INNER JOINdbo.ORG_Unit AS b ON a.UnitID = b.ID 创建部门： createview v_oa_deptment as SELECT b.ID, b.Name, b.Code, e.partmentid, a.CompanyID,

10、b.StatusID FROM dbo.ORG_Department AS a INNER JOINdbo.ORG_Unit AS b ON a.UnitID = b.ID INNER JOIN(SELECT c.ID AS partmentid, d.ID AS id FROM dbo.ORG_Unit AS c INNER JOIN dbo.ORG_Unit AS d ON c.Code=SUBSTRING(d.Code, 1, LEN(d.Code) - CHARINDEX(., REVERSE(d.Code) AS e ON b.ID = e.id创建职位：createview v_o

11、a_jobtitleasSELECT ID, Name, Code, UnitID, GETDATE() AS datatimeFROM dbo.ORG_Positioncreateview v_oa_hrmjob asSELECT ID, EM_ID, PositionlD, BeginDate FROM dbo.HM_EM_PositionHistory AS hob WHERE EXISTS (SELECT 1 AS Expr1FROM (SELECT EM_ID, MAX(BeginDate) AS BeginDate FROM dbo.HM_EM_PositionHistoryGRO

12、UP BY EM_ID) AS bzwb WHERE (EM_ID = hob.EM_ID) AND (BeginDate = hob.BeginDate)创建人员：createview v_oa_hrmresource asSELECT 10 AS aqjb, CASE WHEN e.otherunit != NULL THEN a.code ELSE END AS loginid, b.positionid AS jobid, c.unitid AS depid, d.Type_ AS status, a.EM_ID, a.Name,a.Sex, a.Birthday, a.Code, a.Mobile, a.NativePlace,a.IDCardID, a.Telphone, a.EMail, a.FItemID, a.FParentID, a.DimissionTime, 1 AS loaction, 7 AS yuyan FROM dbo.HM_Employees AS a LEFT OUTER JOINdbo.v_oa_hrmjob AS b ON a.