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 fstaff