s-HR编制常见问题

【看到有编制却提示没有编制】

--员工变动记录已占编制

select relation.* from T_HR_EmpOrgRelation relation

LEFT OUTER JOIN T_HR_EmpLaborRelation labor ON RELATION.FLABORRELATIONID = LABOR.FID

LEFT OUTER JOIN T_HR_BDEmployeeType empType ON labor.FLaborRelationStateID = EMPTYPE.FID

where relation.fisInner = 1 AND relation.fEFFDT <= {TS '2021-05-18 00:00:00'} AND relation.fLEFFDT > {TS '2021-05-18 00:00:00'} AND relation.fisEnrolled = 1 AND empType.FisOnTheStrength = 1 AND relation.FAdminOrgID = '00000000-0000-0000-0000-000000000000CCE7AED4' 

--消耗编制过滤（加） addFilter:

select * from t_hr_empposchangedata where (fbillState IN (1, 2) OR (fbillState = 3 AND fbizDate > {TS '2021-05-18 00:00:00'})) AND fisExcluded = 0 AND fisEnrolled = 1 AND FAdminOrgID = '00000000-0000-0000-0000-000000000000CCE7AED4' AND fcount = 1

--释放编制过滤（减） subFilter:

select * from t_hr_empposchangedata where (fbillState IN (1, 2) OR (fbillState = 3 AND fbizDate > {TS '2021-05-18 00:00:00'})) AND fisExcluded = 0 AND fisEnrolled = 1 AND FAdminOrgID = '00000000-0000-0000-0000-000000000000CCE7AED4' AND fcount = -1

【复用上一年编制——编制复制】

先将脚本中的'2022编制方案的ID'、'2021编制方案的ID'替换成对应的ID

再按序号依次执行：

1、SELECT * into t_hr_staffingorgcopy from t_hr_staffingorg;

2、SELECT * into t_hr_staffingpostcopy from t_hr_staffingpost;

3、INSERT INTO t_hr_staffingorg (FID,FAdminOrgUnit,FStaffing,FYear,FMonth,FAllStaffing,FDirectStaffing,FAllYearStaffing,FDirectYearStaffing,FStartDate,FEndDate,FCreatorID,FCreateTime,FLastUpdateUserID,FLastUpdateTime,FControlUnitID,FState,FAllStaffingDetail,FDirectStaffingDetail) SELECT newbosid('A8B64358'),FAdminOrgUnit

,'2022编制方案的ID',FYear,1,FAllStaffing,FDirectStaffing,FAllYearStaffing,FDirectYearStaffing,{ts'2022-01-01 00:00:00'},{ts'2022-01-31 23:59:59'},FCreatorID,getDate(),FLastUpdateUserID,getDate(),FControlUnitID,FState,FAllStaffingDetail,FDirectStaffingDetail FROM t_hr_staffingorg  where fstaffing = '2021编制方案的ID' and fmonth = 12;

4、INSERT INTO t_hr_staffingpost (FID,FPosition,FAdminOrgUnit,FStaffing,FYear,FMonth,FAllStaffing,FDirectStaffing,FAllYearStaffing,FDirectYearStaffing,FStartDate,FEndDate,FCreatorID,FCreateTime,FLastUpdateUserID,FLastUpdateTime,FControlUnitID,FState,FAllStaffingDetail,FDirectStaffingDetail) SELECT newbosid('6E1292AC'),FPosition,FAdminOrgUnit

,'2022编制方案的ID',FYear,1,FAllStaffing,FDirectStaffing,FAllYearStaffing,FDirectYearStaffing,{ts'2022-01-01 00:00:00'},{ts'2022-01-31 23:59:59'},FCreatorID,getDate(),FLastUpdateUserID,getDate(),FControlUnitID,FState,FAllStaffingDetail,FDirectStaffingDetail FROM t_hr_staffingpost  where fstaffing = '2021编制方案的ID' and fmonth = 12;

【编制数据去重】

//组织编制

delete  from  T_HR_STAFFINGORG where  fid in  (

select  a.fid  from  T_HR_STAFFINGORG  a  inner join  (select fadminorgunit,FSTATE,fstaffing,max(FID) maxid  from T_HR_STAFFINGORG where fstate = 1  and fstaffing ='gLIAAAIqktTIlFvs' group by fadminorgunit,FSTATE,fstaffing  having count(1) >1) b  on  a.fadminorgunit=b.fadminorgunit and a.FSTATE=b.FSTATE and a.fstaffing=b.fstaffing and a.fid !=b.maxid

)

//职位编制

delete  from  t_hr_staffingpost where  fid in  ( 

select a.fid from t_hr_staffingpost a inner join ( select fposition,Fmonth,max(FID) maxid from t_hr_staffingpost where fstate = 1 and fstaffing ='这里填当前启用的编制ID' group by FPOSITION, Fmonth having count(1) >1 ) b on a.fposition=b.Fposition and a.fmonth=b.fmonth and a.fstaffing= '这里填当前启用的编制ID' and a.fstate = 1 and a.fid != b.maxid

)

delete  from  T_HR_AdminOrgBURelation where  fid in  (

select  a.fid  from  T_HR_AdminOrgBURelation  a  

inner join  

(select FADMINORGID,FBIZMANAGETYPEID,FMANAGEHRORGID,FSTATE,FISDEFAULTMANAGE, max(FID) maxid from T_HR_AdminOrgBURelation where  FSTATE =1   

group by FADMINORGID,FBIZMANAGETYPEID,FMANAGEHRORGID,FSTATE,FISDEFAULTMANAGE  having count(*)>1 ) b  

on  a.FADMINORGID=b.FADMINORGID and  a.FBIZMANAGETYPEID=b.FBIZMANAGETYPEID and  a.FMANAGEHRORGID=b.FMANAGEHRORGID and a.FSTATE=b.FSTATE   and  a.FISDEFAULTMANAGE=b.FISDEFAULTMANAGE  and  a.fid !=b.maxid

)

组织：

1、执行以下语句保存原数据

select * into t_hr_staffingorgcopy1 from t_hr_staffingorg

2、将封存组织的编制更新为0

update t_hr_staffingorg set fallstaffing = 0,fdirectstaffing = 0 where fid in

(SELECT sorg.fid FROM t_hr_staffingorg sorg 

left join t_org_admin org

on sorg.fadminorgunit = org.fid 

where org.fissealup = 1

and sorg.fstaffing = '这里填入对应编制表的ID')

职位：

1、执行以下语句保存原数据

select * into t_hr_staffingpostcopy1 from t_hr_staffingpost

2、将禁用组织的编制更新为0

update t_hr_staffingpost set fallstaffing = 0 where fid in 

(SELECT spos.fid FROM t_hr_staffingpost spos 

left join t_org_position post 

on spos.fposition = post.fid 

where post.fdeletedstatus = 2  

and sorg.fstaffing = '这里填入对应编制表的ID' )

编制导入数据值格式必须是数字