【看到有编制却提示没有编制】
--员工变动记录已占编制
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' )
编制导入数据值格式必须是数字
推荐阅读